SUMIF function with 2 conditions

  • Thread starter Thread starter rlandlin
  • Start date Start date
R

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
 
The conditional sum wizard is always an option but this is a little easier:

=SUMPRODUCT(--(A1:A3="Revenue"),--(B1:B3="HQ"),C1:C3)
 
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 said:
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
Click to expand...

Your formula is

=SUMPRODUCT((A1:A3=\"REVENUE\")*(B1:B3=\"HQ\")*C1:C3)

Regards.
 
Back
Top