SUMPRODUCT help

[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

 

[Bernard Liengme]

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

 

[Earl Kiosterud]

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.

 

[Harlan Grove]

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

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))

....

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)