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

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

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

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

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