Problem with SUMPRODUCT

[stevesoul]

I have used the SUMPRODUCT to add up how many times each month an project is
updated. The formula works from months Feb through Dec, although for Jan, it
gives me a number which I cannot understand. The formula for Feb is
=SUMPRODUCT(--(MONTH(B2:B49)=2)). I typed the same formula in Jan and just
changed the number 2 for Feb to a number 1 for Jan. Why does it give me 44
and how could I fix it?

SPL # Date Time
7006 28-Feb 17:30 Jan 44
7007 20-Mar 19:30 Feb 1
7008 12-May 10:30 Mar 1
7009 6-Jun 22:00 Apr 0
May 1
Jun 1
Jul 0
Aug 0
Sep 0
Oct 0
Nov 0
Dec 0

Thank you in advance!

 

[Peo Sjoblom]

Try


=SUMPRODUCT(--(MONTH(B2:B49)=1),--(B2:B49<>""))

excel sees blanks as zero and excel dates start with the fictive date Jan 0
1900 and since blanks are seen as zeros in array formulas you have to either
use ISNUMBER, <>"" or add the YEAR as well



--


Regards,


Peo Sjoblom

 

[Dave Peterson]

If the cell (say A1) is empty, then excel will return a 1 for =month(a1).

You can avoid counting those empty cells as January:
=SUMPRODUCT(--(isnumber(b2:b49),--(MONTH(B2:B49)=1))