How to sum with 2 conditions

A

Alejandro

In the following example how can I add all the comissions for Apartments in
San Francisco or Houses in New York using SumIf for example??

Type Property Value Commission
Apartment San Francisco 7,000
House New York 14,000
Apartment New York 21,000
Apartment San Francisco 13,000
House San Francisco 28,000
Apartment New York 8,000
 
B

Bob Phillips

=SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San
Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20)

for one way
 
D

Domenic

Try...

=SUMPRODUCT(--($A$2:$A$7="Apartment"),--($B$2:$B$7="San
Francisco"),$C$2:$C$7)

or

=SUMPRODUCT(--($A$2:$A$7=E1),--($B$2:$B$7=F1),$C$2:$C$7)

....where E1 contains the 'Type', and F1 contains the 'Property'.

Hope this helps!
 
A

Alejandro

If I use the following formula as you stated (Pivot of data in A1)

=SUMIF(A2:A7,"Apartment",C2:C7)+SUMIF(B2:B7,"San Francisco",C2:C7)

It adds all comissions for Apartments plus all comissions for San Francisco,
I tried other forms of sumif with not the looked result.

What I need is to sumif with a DOBLE conditions for example:
Apartment located in San Francisco

Thanks
 
D

Don Guillett

or try with { }

=SUMPRODUCT(((A2:A20={"Apartment","House"})*(B2:B20={"San
Francisco","New York"})*C2:C20)
 
B

Bob Phillips

I read it as Apartments in San Fran, OR Houses in NY, not Apartments or
Houses in San Fran or NY, Actually, it wasn't just the way I read it, he
said that.
 
E

Earl Kiosterud

Alejandro:

=SUMPRODUCT( (A2:A10="Apartment") * (B2:B10="San Francisco") * (C2:C10) )
 
Top