SUMPRODUCT help

  • Thread starter Thread starter JOSA
  • Start date Start date
J

JOSA

Help please

I'm trying to count the number on items sold on a particular month
using SUMPRODUCT but I'm getting the wrong answer

This is what I use for each month (Feburary) for this example:

=SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4))

The item is in A4 the date is in column L and in Column U are all the
mixed items.
I have checked the date format and its ok.

Can any one help please?

Thanks
josa
 
Odd. I would begin by testing each part of the formula. For example:
1) =SUMPRODUCT((MONTH($L$2:$L$5973)=2))
2) =SUMPRODUCT((YEAR($L$2:$L$5973)=2007))
3) =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007))
etc
best wishes
 
Josa,

Some possibilities:

THe dates in column L aren't really dates. In a cell, try =MONTH(L55) to
see if you get the correct month digit for that date.

The item in A4 doesn't exactly match anything in column U. Try =A4=L55
(change L55 to the correct cell). You should see TRUE.
 
JOSA said:
I'm trying to count the number on items sold on a particular month
using SUMPRODUCT but I'm getting the wrong answer
Click to expand...

Wrong how? Does the formula returns an incorrect though numeric result
or an error value? If an incorrect numeric result, is it zero or a
positive number less than what it should be?
This is what I use for each month (Feburary) for this example:

=SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)
*($U$2:$U$5973=$A4))
Click to expand...
....

Not that this is a fix, but you don't need separate month and year
checks. You could try

=SUMPRODUCT((TEXT($L$2:$L$5973,"yyyymm")="200702")*($U$2:$U$5973=$A4))

or (getting tricky and obscure but also more efficient)

=SUMPRODUCT((ABS($L$2:$L$5973-"2007-02-14"-0.5)<14)*($U$2:$U$5973=$A4))

Does the following formula return what you believe should be the
correct total number of items in column U?

=COUNTIF($U$2:$U$5973,$A4)
 
Back
Top