may be SP!

[via135]

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

 

[Bob Phillips]

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

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A11>=DATE(2006,1,1)), --(A1:A11<=DATE(2006,4,1)),
-((B1:B11=2)+(B1:B11=4)-1), C1:C11)

 

[via135]

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

 

[via135]

thks McG!

i am able to get the result..but Bob's function seems to be elegant..!

regds!

-via135

 

[JE McGimpsey]

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