sumif with two conditions
 

Hi all,

I have a question. I'm trying to use SUMIF with two criteria and I'm not
sure how to do it. I have the following.

Age Amount
25 10,000
30 10,000
32 20,000
33 25,000
42 40,000

I'm trying to do a sumif to add up all the amounts of people with < 30 which
works fine. Next I want to sum all the amounts of people with age 29 and
<37.

I tried sumif(A1:A5,AND("29","<37"),B1:B5) but that just returns 0.

Any help would be appreciated. I'm not sure if a sumif is the best way to
go. I thought about maybe sumproduct but I'm not sure how to set that up.
Thanks.

Try in a third column (C) to use the formula =IF(AND(A129,A1<37),B1,0)
Then, just sum column C.

"ww" wrote:

Hi all,

I have a question. I'm trying to use SUMIF with two criteria and I'm not
sure how to do it. I have the following.

Age Amount
25 10,000
30 10,000
32 20,000
33 25,000
42 40,000

I'm trying to do a sumif to add up all the amounts of people with < 30 which
works fine. Next I want to sum all the amounts of people with age 29 and
<37.

I tried sumif(A1:A5,AND("29","<37"),B1:B5) but that just returns 0.

Any help would be appreciated. I'm not sure if a sumif is the best way to
go. I thought about maybe sumproduct but I'm not sure how to set that up.
Thanks.

=sumproduct(--(a1:a100<37),--(a1:a10029),b1:b100)
where a1:a100 are age and b1:b100 are the amount

"ww" wrote in message
...
Hi all,

I have a question. I'm trying to use SUMIF with two criteria and I'm not
sure how to do it. I have the following.

Age Amount
25 10,000
30 10,000
32 20,000
33 25,000
42 40,000

I'm trying to do a sumif to add up all the amounts of people with < 30
which
works fine. Next I want to sum all the amounts of people with age 29 and
<37.

I tried sumif(A1:A5,AND("29","<37"),B1:B5) but that just returns 0.

Any help would be appreciated. I'm not sure if a sumif is the best way to
go. I thought about maybe sumproduct but I'm not sure how to set that up.
Thanks.

Try

=SUMIF(A1:A5, "29",B1:B5) - SUMIF(A1:A5, "=37",B1:B5)

Steve


"ww" wrote in message
...
Hi all,

I have a question. I'm trying to use SUMIF with two criteria and I'm not
sure how to do it. I have the following.

Age Amount
25 10,000
30 10,000
32 20,000
33 25,000
42 40,000

I'm trying to do a sumif to add up all the amounts of people with < 30
which
works fine. Next I want to sum all the amounts of people with age 29 and
<37.

I tried sumif(A1:A5,AND("29","<37"),B1:B5) but that just returns 0.

Any help would be appreciated. I'm not sure if a sumif is the best way to
go. I thought about maybe sumproduct but I'm not sure how to set that up.
Thanks.