SUMIF function with 2 conditions

[rlandlin]

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

 

[Guest]

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

 

[Guest]

The conditional sum wizard is always an option but this is a little easier:

=SUMPRODUCT(--(A1:A3="Revenue"),--(B1:B3="HQ"),C1:C3)

 

[Guest]

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

 

[BenjieLop]

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.