SUMPRODUCT help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JOSA - 24 Jan 2007 13:58 GMT

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

Reply to this Message

Bernard Liengme - 24 Jan 2007 14:28 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Help please
>
[quoted text clipped - 13 lines]
> Thanks
> josa

Reply to this Message

Earl Kiosterud - 24 Jan 2007 18:25 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> Help please
>
[quoted text clipped - 13 lines]
> Thanks
> josa

Reply to this Message

Harlan Grove - 24 Jan 2007 19:02 GMT

"JOSA" <jc2...@hotmail.com> wrote...

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

Reply to this Message

Rate this thread: