may be SP!


V

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
 
Ad

Advertisements

B

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

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

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
 
V

via135

thks McG!

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

regds!

-via135
 
Ad

Advertisements

J

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top