Sumproduct Function problem

  • Thread starter Thread starter JRod
  • Start date Start date
J

JRod

Hi, guys,
I have a Range A1:A10 with the following:
A1 - 01-Jan
A2 - 02 -Jan
A3 - 01-Feb
A4 - 02-Feb
A5 (Blank)
A6(Blank)
A7(Blank)
A8 - 03-Jan
A9(Blank)
A10(Blank)

If I want to count how many cells have "Jan" with the formula:
=SUMPRODUCT(--(TEXT($A$1:$A$5;"mmm")="Jan"))

it gives me the number 8. However, really the number is 3.
But, if the above formula is with "Feb", the number is correct, i.e. - 2.

I understood that for unknown reason to me, the first formula counts the
blank cells as they are with "Jan" too. So, if I write:
=SUMPRODUCT(--(TEXT($A$1:$A$10;"mmm")="Jan"))-SUMPRODUCT(--ISBLANK(A1:A10))
the result is now correct, I mean, number 3.

Any ideas for this? Thanks in advance.
 
Here is an alternative.

=SUMPRODUCT((MONTH(A1:A5)=1)*(ISNUMBER(A1:A5)))

This at least uses one fewer function call than your alternative. But yes
you're right, if you don't check for the content of the range, Excel will
for some reason consider blank cells to be equal to 0 in the MONTH(A1:A5)
part, which is a January date a long time ago.
 
Thanks, Dave
I really didn't know that 0 was a January date a long time ago.

--
JRod

Dave R. said:
Here is an alternative.

=SUMPRODUCT((MONTH(A1:A5)=1)*(ISNUMBER(A1:A5)))

This at least uses one fewer function call than your alternative. But yes
you're right, if you don't check for the content of the range, Excel will
for some reason consider blank cells to be equal to 0 in the MONTH(A1:A5)
part, which is a January date a long time ago.
Click to expand...
 
An empty cell is read off as 0, a result that gets mapped to a January date.

Assuming that you have true dates in A1:A10 and you're interested to
count, say, Jan 2005 dates.

In C1 enter the first of the month/year of interest as a true date: 1-Jan-05

In C2 enter:

=SUMPRODUCT(--(DATE(YEAR(A1:A10),MONTH(A1:A10),1)=C1))

The result should be 3.
 
Back
Top