Formula giving wrong result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
 
Jock,

This is because Excel recognizes the fictitious date 0 Jan 1900 and is
the number 0. There exists adequate reasoning behind this design
choice. For the time being, you can count the Jan dates excluding
blank cells with:

=SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<>""))

HTH
Kostis Vezerides
 
Try =month(a1) when A1 is empty.

=SUMPRODUCT(--(isnumber(b$7:b$998)),--(MONTH(B$7:B$998)=1))
 
Hi Jock

From a date viewpoint, Excel treats a blank cell as 00/01/1900 hence Month
1.

Try
=SUMPRODUCT(--(MONTH(B$7:B$998)=1),--(B$7:B$998<>""))
 
Thanks guys.

Next Q
How do I get this:
=COUNTIF($D5,(MIN($D$4:$D$15)))
to ignore zero's?
--
Traa Dy Liooar

Jock


vezerid said:
Jock,

This is because Excel recognizes the fictitious date 0 Jan 1900 and is
the number 0. There exists adequate reasoning behind this design
choice. For the time being, you can count the Jan dates excluding
blank cells with:

=SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<>""))

HTH
Kostis Vezerides
Click to expand...
 
Back
Top