Solved How to use =COUNTBLANK with days of the month

Dell / Inspiron 17r
August 8, 2020 at 07:28:27
Specs: windows 10, 2.3/8
 I have a spreadsheet that has 12 rows for months and 31 columns for days of the month. Each cell contains either a value or is blank. I can count the number of cells that have values. I tried using =COUNTBLANK to count the number of blank cells in each row but that only worked correctly if I changed the range of cells to correspond to the number of days in each month. Is there a way that one formula can be used for all twelve months?Thank you.Brian W

See More: How to use =COUNTBLANK with days of the month

August 9, 2020 at 19:25:55
 And just for the fun of it, here is another way using COUNTA=DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(\$A3&" 1")),1),0))-COUNTA(O3:AS3)MIKEhttp://www.skeptic.com/

#1
August 8, 2020 at 08:08:13
 Since the longest month has 31 days, you can set the range to COUNTBLANKS over the full 31 cells and then subtract the blanks caused by the fewer days in other months.Assuming"``` A ..... AC AD AE 1 1/1/2020 1/29/2020 1/30/2020 1/31/2020 2 2/1/2020 2/29/2020 etc. ```=COUNTBLANK(A1:AE1)-(31-DAY(EOMONTH(A1,0)))message edited by DerbyDad03

#2
August 8, 2020 at 16:03:07
 Without an example of what your data looks like, I'm guessing, from your description, it looks something like: ``` A B C D E F AD AE AF 1)Mnth/Day 1st 2nd 3rd 4th 5th ... 29th 30th 31st 2)Jan data data ... data data 3)Feb data data ... data data 4)Mar data data ... data data 5)Apr data data data ... data 6)May data data data ... data 7)Jun data data data ... data 8)Jul data data data ... data 9)Aug data data data ... data 10)Sept data data ... data 11)Oct data data ... data data 12)Nov data data ... data data 13)Dec data data ... data data ```If this is how it looks, then this formula should get you what you want: =COUNTBLANK(B2:INDIRECT(ADDRESS(ROW(A2),DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(A2&" 1")),1),0))+1)))Drag down 12 rows to get each months figures.It is rather long and complex so just copy it from here.MIKEmessage edited by mmcconaghy

#3
August 8, 2020 at 17:00:13
 Using Mike's layout, you could use my formula by entering your months in Column A as dates, e.g. 1/1/2020 and formatting the cells as mmmm to display the full month name or mmm to display the 3 letter abbreviation. The Day and Year don't really matter since you are only displaying the Month. They are only needed because DAY(EOMONTH(...)) needs to see an actual Date.=COUNTBLANK(B2:AF2)-(31-DAY(EOMONTH(A2,0)))(Not that there is anything wrong with Mike's formula. :-) It's simply a choice of a long formula that works with a text Month vs a shorter one that requires an extra formatting step.)message edited by DerbyDad03

Related Solutions

#4
August 8, 2020 at 18:09:30
 It's simply a choice of a long formula that works with a text Month vs a shorter one that requires an extra formatting step.That's the problem when you don't have an example of thespreadsheet and you have to guess as to how the sheet looks. :-)MIKEmessage edited by mmcconaghy

#5
August 8, 2020 at 19:23:59
 DerbyDad03 and mmcconaghy,Sorry for any confusion that I might have caused. How can I send you a copy of my spreadsheet so that the confusion will be eliminated?Thank you.Brian W

#6
August 8, 2020 at 20:02:03
 Can't you just post some sample data like Mike & I did? We don't need all the data, just an example of the layout.Otherwise, upload the file to a file sharing site (something that we don't have to register for) and post the link back here. Or take a screen shot and post the image link here in the forum.message edited by DerbyDad03

#7
August 9, 2020 at 10:24:18

#8
August 9, 2020 at 11:36:58
 Some additional pointers if you plan to use the .PRN filetype.When you paste your example data into the new workbook,paste it into cell B2, that will allow you to add theColumn Letters across Row 1Row Numbers down Column AYou can also format your Row Numbers with a custom format of:` #)`so as to have a space between the row number and the data.Then when you Save As a .PRN you should have only minorif any changes to make.MIKEhttp://www.skeptic.com/

#9
August 9, 2020 at 16:38:40
 mmcconaghy,I used File Convoy to send you a copy of the spreadsheet.http://www.fileconvoy.com/dfl.php?i...Thank you,Brian W

#10
August 9, 2020 at 18:24:16
 Put this formula in cell M3 and drag down 12 rows:=COUNTBLANK(O3:INDIRECT(ADDRESS(ROW(A3),DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(A3&" 1")),1),0))+13)))See how that works for you.Giving inconsistent results. MIKEmessage edited by mmcconaghy

#11
August 9, 2020 at 18:34:06
 Brian,Thank you for uploading your spreadsheet.A suggestion: When posting (or uploading) example data, it often helps if you tell us what output you are expecting based on that data. If we have a target to shoot for, we have a better chance of providing a working solution the first time. I pretty sure that my results below are what you are looking for, but I can't be absolutely sure unless you tell us. If you had told us upfront, I'd be 100% sure.Using my formatting method and my formula, adjusted for your ranges, I get:=COUNTBLANK(O3:AS3)-(31-DAY(EOMONTH(A3,0)))Drag it down to M14 ``` A M 1 8/9/2020 2 Blanks 3 January 13 4 February 23 5 March 15 6 April 4 7 May 3 8 June 1 9 July 2 10 August 24 11 September 30 12 October 31 13 November 30 14 December 31 ```I'll leave it up to Mike to adjust his fine formula based on the ranges in your spreadsheet.message edited by DerbyDad03

#12
August 9, 2020 at 18:47:38
 Hold off on my formula, it is giving inconsistent results.Use DerbyDad's suggestion & formula.MIKEhttp://www.skeptic.com/

#13
August 9, 2020 at 18:59:08
 Must learn how to do simple math...This one seems to work;=COUNTBLANK(\$O3:INDIRECT(ADDRESS(ROW(\$A3),DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(\$A3&" 1")),1),0))+14)))MIKEhttp://www.skeptic.com/

#14
August 9, 2020 at 19:19:37
 Here is DerbyDad's formula modified for your circumstances:=COUNTBLANK(O3:AS3)-(31-DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(\$A3&" 1")),1),0)))No need to change column AMIKEhttp://www.skeptic.com/

#15
August 9, 2020 at 19:25:55
 And just for the fun of it, here is another way using COUNTA=DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(\$A3&" 1")),1),0))-COUNTA(O3:AS3)MIKEhttp://www.skeptic.com/

#16
August 10, 2020 at 07:42:45
 mike:Can't we shorten your formula in #14 to this?=COUNTBLANK(O3:AS3)-(31-DAY(EOMONTH(DATEVALUE(A3&"1, 2020"),0)))As you pointed out, your formula works with Text based months in Column A. It looks like the shortened version does also, unless I missed something, of course. UPDATE: This formula will probably not work correctly since the year is hard coded to 2020, which is a leap year. It will only work correctly in leap years, so I suggest that you use one of Mike's versions where the current Year is calculated and let Excel determine how many day's are in February.message edited by DerbyDad03

#17
August 10, 2020 at 10:11:00
 DerbyDad03The difference between the two formula is that I was trying not to hard code any values, but your modification is now a 4th way to do the same thing. :-)MIKEmessage edited by mmcconaghy

#18
August 10, 2020 at 10:57:26
 DerbyDad03 and mmcconaghy,Thank for you both for resolving this issue.Brian W