Dell / Inspiron 17r

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

✔ 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

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

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 dataIf 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

message edited by mmcconaghy

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 mmmmto display the full month name ormmmto 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

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

exampleof the

spreadsheet and you have to guess as to how the sheet looks. :-)MIKE

message edited by mmcconaghy

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

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

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 aSave Asand selectOther Formats,Next, at the bottom of the popup, in the box that reads

Save as Type

Scroll down until you find:and save your workbook as aFormatted Text (Space Delimited) (*.PRN).PRNfiletype.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.

NOTEDo 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 addRow Numbers & Column Lettersto 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

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 Lettersacross Row 1Row Numbersdown 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

mmcconaghy, I used File Convoy to send you a copy of the spreadsheet.

http://www.fileconvoy.com/dfl.php?i...

Thank you,

Brian W

~~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

message edited by mmcconaghy

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 31I'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

Hold off on my formula, it is giving inconsistent results.

Use DerbyDad's suggestion & formula.MIKE

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

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

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

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

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

message edited by mmcconaghy

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

Ask Your Question

Weekly Poll

Are you looking forward to the Xbox Series X?

Discuss in The Lounge

Poll History