Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert values based on 2 criterias | Excel Discussion (Misc queries) | |||
Too many criterias... | Excel Worksheet Functions | |||
Sum If using 2 criterias | Excel Discussion (Misc queries) | |||
Counting instances based on two criterias | Excel Worksheet Functions | |||
Delete rows based on multiple criterias | Excel Discussion (Misc queries) |