sum based on 4 criterias

[freebee]

Hi, I need to sum the following:
Sum qty in Col. A if Col.B shows June dates, but exclues if Col.C=X and Y,
and exclude if Col.D=Z
I'm using Excel 2003.
Can someone help? Thank you.

 

[Max]

Something like this, in say E2:
=SUMPRODUCT((TEXT(B2:B5,"mmmyy")="Jun09")*(ISERROR(MATCH(C2:C5,{"X";"Y"},0)))*(D25<>"Z"),A2:A5)
Adapt the ranges to suit

Celebrate your success, click the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[freebee]

Hi, I tried it and it returns 0. My date is entered as 2009-6-1, 2009-6-5,
etc.
Could that be the problem?
Thanks.

 

[Max]

Yes, its critical. Believe you are not entering the dates as real dates
recognized by Excel. To convert it all (what you have entered) at one go to
real dates, select the col range of "dates", click Data > Text to Columns.
Click Next>Next. In step 3 of the wiz., under Column data format, check
"Date", then choose from the droplist: YMD, click Finish. The earlier
expression should now work beautifully.

Play it safe when you are keying-in dates. Never be lazy. Always key-in the
FULL date, inclusive of the month and the year.

I'd key-in dates like this:
28Jun2009
using an unambiguous "mmm" for the month, and "yyyy" for the year
I won't go wrong doing this.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[T. Valko]

Will the dates all be within the same year?

See if this works:

=SUMPRODUCT(--(MONTH(B2:B20)=6),--(ISNA(MATCH(C2:C20,{"X","Y"},0))),--(D220<>"Z"),A2:A20)