sum based on 4 criterias
 

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.

sum based on 4 criterias
 

Something like this, in say E2:
=SUMPRODUCT((TEXT(B2:B5,"mmmyy")="Jun09")*(ISERROR (MATCH(C2:C5,{"X";"Y"},0)))*(D2:D5<"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" wrote:
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

sum based on 4 criterias
 

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" wrote:

Something like this, in say E2:
=SUMPRODUCT((TEXT(B2:B5,"mmmyy")="Jun09")*(ISERROR (MATCH(C2:C5,{"X";"Y"},0)))*(D2:D5<"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" wrote:
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

sum based on 4 criterias
 

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 NextNext. 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
---
"freebee" wrote:
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.

sum based on 4 criterias
 

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))),--(D2:D20<"Z"),A2:A20)

--
Biff
Microsoft Excel MVP


"freebee" wrote in message
...
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" wrote:

Something like this, in say E2:
=SUMPRODUCT((TEXT(B2:B5,"mmmyy")="Jun09")*(ISERROR (MATCH(C2:C5,{"X";"Y"},0)))*(D2:D5<"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" wrote:
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