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

=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

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

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 -

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 -

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