Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF function with 2 conditions
Hi, I need a function to give two conditions in a SUMIF formula and don't get round it on my own. I need to calculate e.g. if column A is Revenue and column B is HQ, then sum up column C. A B C 1 Revenue HQ 250 2 Revenue Loc 350 3 Expense HQ 450 Maybe it's not the SUMIF formula to solve the matter, but you know a more suitable one, I'm happy to learn about it. Thanks for you support, Ruth -- rlandlin ------------------------------------------------------------------------ rlandlin's Profile: http://www.excelforum.com/member.php...o&userid=27596 View this thread: http://www.excelforum.com/showthread...hreadid=471440 |
#2
|
|||
|
|||
Using the Conditional Sum Wizard is the easiest option. This is an add-in.
Please refer to the following link to get a detailed explanation. http://www.mrexcel.com/tip083.shtml Jeff "rlandlin" wrote: Hi, I need a function to give two conditions in a SUMIF formula and don't get round it on my own. I need to calculate e.g. if column A is Revenue and column B is HQ, then sum up column C. A B C 1 Revenue HQ 250 2 Revenue Loc 350 3 Expense HQ 450 Maybe it's not the SUMIF formula to solve the matter, but you know a more suitable one, I'm happy to learn about it. Thanks for you support, Ruth -- rlandlin ------------------------------------------------------------------------ rlandlin's Profile: http://www.excelforum.com/member.php...o&userid=27596 View this thread: http://www.excelforum.com/showthread...hreadid=471440 |
#3
|
|||
|
|||
The conditional sum wizard is always an option but this is a little easier:
=SUMPRODUCT(--(A1:A3="Revenue"),--(B1:B3="HQ"),C1:C3) -- Regards, Dave "rlandlin" wrote: Hi, I need a function to give two conditions in a SUMIF formula and don't get round it on my own. I need to calculate e.g. if column A is Revenue and column B is HQ, then sum up column C. A B C 1 Revenue HQ 250 2 Revenue Loc 350 3 Expense HQ 450 Maybe it's not the SUMIF formula to solve the matter, but you know a more suitable one, I'm happy to learn about it. Thanks for you support, Ruth -- rlandlin ------------------------------------------------------------------------ rlandlin's Profile: http://www.excelforum.com/member.php...o&userid=27596 View this thread: http://www.excelforum.com/showthread...hreadid=471440 |
#4
|
|||
|
|||
Hi,
Use the following formula; modify the A, B, and C ranges appropriately. =SUMPRODUCT(--($A$1:$A$100="Revenue"),--($B$1:$B$100="HQ"),$C$1:$C$100) A convenient variation would be, enter 'Revenue' and 'HQ' in D1 and E1 (or some other cells convenient for you) respectively, and use the formula, =SUMPRODUCT(--($A$1:$A$100=D1),--($B$1:$B$100=E1),$C$1:$C$100) Here, you may change the contents of D1 and E1 for other possibilities (e.g., "Expense' and 'loc', etc.) without having to change the formula each time. Regards, B. R. Ramachandran Regards, B. R. Ramachandran "rlandlin" wrote: Hi, I need a function to give two conditions in a SUMIF formula and don't get round it on my own. I need to calculate e.g. if column A is Revenue and column B is HQ, then sum up column C. A B C 1 Revenue HQ 250 2 Revenue Loc 350 3 Expense HQ 450 Maybe it's not the SUMIF formula to solve the matter, but you know a more suitable one, I'm happy to learn about it. Thanks for you support, Ruth -- rlandlin ------------------------------------------------------------------------ rlandlin's Profile: http://www.excelforum.com/member.php...o&userid=27596 View this thread: http://www.excelforum.com/showthread...hreadid=471440 |
#5
|
|||
|
|||
rlandlin Wrote: Hi, I need a function to give two conditions in a SUMIF formula and don't get round it on my own. I need to calculate e.g. if column A is Revenue and column B is HQ, then sum up column C. A B C 1 Revenue HQ 250 2 Revenue Loc 350 3 Expense HQ 450 Maybe it's not the SUMIF formula to solve the matter, but you know a more suitable one, I'm happy to learn about it. Thanks for you support, Ruth Your formula is =SUMPRODUCT((A1:A3=\"REVENUE\")*(B1:B3=\"HQ\")*C1: C3) Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=471440 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use IF function for two conditions | Excel Worksheet Functions | |||
I need help with the Sumif Function | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |