|
may be SP!
hi!
i'm having the following data thru A1:C11 what i want is the sum of amt between a given period but not equal to some codes! for example the sum of amt for the period 01/01/2006 to 01/04/2006 (inclusive of both dates) for the codes not euqal to 2 & 4 ie. the result should be 140..! date code amt 01/01/2006 1 10 01/01/2006 3 20 02/02/2006 4 30 03/03/2006 1 50 30/03/2006 3 60 01/04/2006 2 40 02/05/2006 2 30 02/05/2006 4 20 01/07/2006 1 10 04/07/2006 2 50 help pl.?! regds! -via135 |
may be SP!
=SUMPRODUCT(--(A2:A20=--"2006-01-01"),--(*A2:A20<=--"2006-04-01"),--(NOT(IS
NUMBER(MATCH(B2:B20,{2,4},0)))),C2:C20) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "via135" wrote in message ups.com... hi! i'm having the following data thru A1:C11 what i want is the sum of amt between a given period but not equal to some codes! for example the sum of amt for the period 01/01/2006 to 01/04/2006 (inclusive of both dates) for the codes not euqal to 2 & 4 ie. the result should be 140..! date code amt 01/01/2006 1 10 01/01/2006 3 20 02/02/2006 4 30 03/03/2006 1 50 30/03/2006 3 60 01/04/2006 2 40 02/05/2006 2 30 02/05/2006 4 20 01/07/2006 1 10 04/07/2006 2 50 help pl.?! regds! -via135 |
may be SP!
One way:
=SUMPRODUCT(--(A1:A11=DATE(2006,1,1)), --(A1:A11<=DATE(2006,4,1)), -((B1:B11=2)+(B1:B11=4)-1), C1:C11) In article . com, "via135" wrote: hi! i'm having the following data thru A1:C11 what i want is the sum of amt between a given period but not equal to some codes! for example the sum of amt for the period 01/01/2006 to 01/04/2006 (inclusive of both dates) for the codes not euqal to 2 & 4 ie. the result should be 140..! date code amt 01/01/2006 1 10 01/01/2006 3 20 02/02/2006 4 30 03/03/2006 1 50 30/03/2006 3 60 01/04/2006 2 40 02/05/2006 2 30 02/05/2006 4 20 01/07/2006 1 10 04/07/2006 2 50 help pl.?! regds! -via135 |
may be SP!
yes Bob!
i got the result from tweak one..! =SUMPRODUCT(--(A2:A11=--"2006-01-01"),--(A2:A11<=--"2006-04-01"),-- (NOT(ISNUMBER(MATCH(B2:B11,{2,4},0)))),C2:C11) thks again..!!! -via135 On Feb 8, 10:14 pm, "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20=--"2006-01-01"),--(*A2:A20<=--"2006-04-01"),--(NOT(I*S NUMBER(MATCH(B2:B20,{2,4},0)))),C2:C20) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "via135" wrote in message ups.com... hi! i'm having the following data thru A1:C11 what i want is the sum of amt between a given period but not equal to some codes! for example the sum of amt for the period 01/01/2006 to 01/04/2006 (inclusive of both dates) for the codes not euqal to 2 & 4 ie. the result should be 140..! date code amt 01/01/2006 1 10 01/01/2006 3 20 02/02/2006 4 30 03/03/2006 1 50 30/03/2006 3 60 01/04/2006 2 40 02/05/2006 2 30 02/05/2006 4 20 01/07/2006 1 10 04/07/2006 2 50 help pl.?! regds! -via135- Hide quoted text - - Show quoted text - |
may be SP!
thks McG!
i am able to get the result..but Bob's function seems to be elegant..! regds! -via135 On Feb 8, 10:20 pm, JE McGimpsey wrote: One way: =SUMPRODUCT(--(A1:A11=DATE(2006,1,1)), --(A1:A11<=DATE(2006,4,1)), -((B1:B11=2)+(B1:B11=4)-1), C1:C11) In article . com, "via135" wrote: hi! i'm having the following data thru A1:C11 what i want is the sum of amt between a given period but not equal to some codes! for example the sum of amt for the period 01/01/2006 to 01/04/2006 (inclusive of both dates) for the codes not euqal to 2 & 4 ie. the result should be 140..! date code amt 01/01/2006 1 10 01/01/2006 3 20 02/02/2006 4 30 03/03/2006 1 50 30/03/2006 3 60 01/04/2006 2 40 02/05/2006 2 30 02/05/2006 4 20 01/07/2006 1 10 04/07/2006 2 50 help pl.?! regds! -via135- Hide quoted text - - Show quoted text - |
may be SP!
There's usually more than one way to accomplish a goal in XL, and
elegance is in the eye of the beholder... The two functions are very similar. My formula using arithmetic operators may be a bit more efficient than using ISNUMBER(MATCH(...)), but they get to exactly the same result. OTOH, if it is more efficient, it's possibly offset by my having used DATE() functions rather than the implicit string conversions in --"<date". So seems like sixes to me... In article om, "via135" wrote: i am able to get the result..but Bob's function seems to be elegant..! |
| All times are GMT +1. The time now is 04:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com