Formula Help please!

[Stejam]

Hi. I have a set of dates in column D and I have this formula that counts the volume for each month from the whole column, it works great, but for January it returns 9999? Please can anyone figure out why?

=SUMPRODUCT(--(MONTH(D1:D10000)=1))

If you change the last number to 2 or 3 or 4 etc etc it counts February, March, April occurences etc etc but as I say with 1 in there for January it returns 9999?

 

[Rick Rothstein]

Hi. I have a set of dates in column D and I have this formula that counts the volume for each month from the whole column, it works great, but for January it returns 9999? Please can anyone figure out why?

=SUMPRODUCT(--(MONTH(D1:D10000)=1))

If you change the last number to 2 or 3 or 4 etc etc it counts February, March, April occurences etc etc but as I say with 1 in there for January it returns 9999?

The problem is all of the blank cells in the range. When you apply the MONTH function to a blank cell, Excel converts the blank to the number 0 and uses that as the day number with the start year (1900) and start month (January)...

DATE(1900,1,0) ===> 1/0/1900

Hence, the MONTH function evaluate the month for 0 as 1... your formula is counting all the blanks in the column as being Janary. You need to add a test to your formula to make it work correctly...

=SUMPRODUCT(--(MONTH(D1:D10000)=1),--(D1:D10000<>""))

 

[Aladin Akyurek]

Better to include the year too in your test, unless irrelevant...

=COUNTIFS(D:D,">="&K1,D:D,"<="&EOMONTH($K1,0))

where K1 houses a first day date like 2017-01-01.

 

[Stejam]

Hi. I inserted the new formula but it now returns #VALUE .

 

[Aladin Akyurek]

Hi. I inserted the new formula but it now returns #VALUE .

date							2017-01-01	2
2018-01-01
2017-01-02
2017-01-07
2017-02-05

<tbody>
</tbody>

L1 contains the formula:

=COUNTIFS(D:D,">="&K1,D:D,"<="&EOMONTH($K1,0))

 

[Rick Rothstein]

Hi. I inserted the new formula but it now returns #VALUE .

Which new formula was that... mine or Aladin's ?

 

[Stejam]

The penny has dropped now guys, thanks for explaining Rick how this formula actually works...I got this to work from your explanation and logic.

=SUMPRODUCT(--((MONTH($A$2:$A$50)=1)-($A$2:$A$50="")))

It was asked by another user and I now see how this works.

 

[Aladin Akyurek]

The penny has dropped now guys, thanks for explaining Rick how this formula actually works...I got this to work from your explanation and logic.

=SUMPRODUCT(--((MONTH($A$2:$A$50)=1)-($A$2:$A$50="")))

It was asked by another user and I now see how this works.

Then it should be rather:

=SUMPRODUCT((MONTH($D$2:$D$50)=1)-($D$2:$D$50=""))

not what you have.

By the way, this will deliver 3 for the sample I posted. Is that what you want: count in all Januaries, irrespective the years they belong to?