Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ww
 
Posts: n/a
Default 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.

  #2   Report Post  
Jane
 
Posts: n/a
Default

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.

  #3   Report Post  
N Harkawat
 
Posts: n/a
Default

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



  #4   Report Post  
Steve R
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
SumIF w/ two conditions (not the same as other posts!... I think) MeatLightning Excel Discussion (Misc queries) 7 February 8th 05 10:55 PM
SUMIF with two conditions ? Mestrella31 Excel Discussion (Misc queries) 9 December 22nd 04 01:09 AM
Sum(if ... multiple conditions ... Interpretation? Ken Excel Discussion (Misc queries) 6 December 16th 04 11:23 PM
SUMIF based on 2 conditions TimH Excel Worksheet Functions 3 October 28th 04 08:18 PM


All times are GMT +1. The time now is 08:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"