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


✔ Best Answer
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)

MIKE

http://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)))


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#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.)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

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 the
spreadsheet and you have to guess as to how the sheet looks. :-)

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#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


Reply ↓  Report •

#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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#7
August 9, 2020 at 10:24:18
The easiest way I have found is to save your example data as a .PRN filetype:

First, it is important that you first copy/paste your example data into a new workbook,
then do a Save As and select Other Formats,

Next, at the bottom of the popup, in the box that reads Save as Type
Scroll down until you find: Formatted Text (Space Delimited) (*.PRN)
and save your workbook as a .PRN filetype.

Excel may give you a warning about formatting issues, just ignore them.

Now find your new .PRN file and using a Text Editor (like JEdit or Notepad++)
or you can use plain old MS Notepad, open the file, all your data should be more or less aligned properly.

NOTE Do note use MS Word as it may change any quote marks to Smart Quotes and Excel does not like Smart Quotes and may result in error messages when copy/pasted back into a spreadsheet.

If needed make whatever minor adjustments that you feel are needed
and remember to add Row Numbers & Column Letters to make it more readable,
then post on the forum and don’t forget to enclose your data using the PreTags.

See this How-To for using PreTags:

https://www.computing.net/howtos/sh...

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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 the
Column Letters across Row 1
Row Numbers down Column A
You can also format your Row Numbers with a custom format of:

    #)<Space Character>

so as to have a space between the row number and the data.

Then when you Save As a .PRN you should have only minor
if any changes to make.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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


Reply ↓  Report •

#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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

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

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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)))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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 A

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#15
August 9, 2020 at 19:25:55
✔ Best Answer
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)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#17
August 10, 2020 at 10:11:00
DerbyDad03

The 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. :-)

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#18
August 10, 2020 at 10:57:26
DerbyDad03 and mmcconaghy,

Thank for you both for resolving this issue.

Brian W


Reply ↓  Report •

#19
August 12, 2020 at 10:42:32
Brian

Just noticed that you use COUNTA() in the Rides column (column C)
if you wish you can shorten my formula, that uses COUNTA(), like this:

=DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE($A3&" 1")),1),0))-C3

We are simply referencing your COUNTA() instead of re-calculating it.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#20
August 12, 2020 at 11:12:22
mmcconaghy,

Thank you for your assistance.

Brian W


Reply ↓  Report •

Ask Question