Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to sum with 2 conditions
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 |
#3
|
|||
|
|||
=SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San
Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20) for one way -- HTH Bob Phillips "Alejandro" wrote in message ... 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 |
#4
|
|||
|
|||
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! In article , Alejandro wrote: 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 |
#5
|
|||
|
|||
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 "Don Guillett" wrote: A simple way would be to use =sumif+sumif -- Don Guillett SalesAid Software "Alejandro" wrote in message ... 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 |
#6
|
|||
|
|||
or try with { }
=SUMPRODUCT(((A2:A20={"Apartment","House"})*(B2:B2 0={"San Francisco","New York"})*C2:C20) -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20) for one way -- HTH Bob Phillips "Alejandro" wrote in message ... 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 |
#7
|
|||
|
|||
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. -- HTH Bob Phillips "Don Guillett" wrote in message ... or try with { } =SUMPRODUCT(((A2:A20={"Apartment","House"})*(B2:B2 0={"San Francisco","New York"})*C2:C20) -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20) for one way -- HTH Bob Phillips "Alejandro" wrote in message ... 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 |
#8
|
|||
|
|||
Alejandro:
=SUMPRODUCT( (A2:A10="Apartment") * (B2:B10="San Francisco") * (C2:C10) ) -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Alejandro" wrote in message ... 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 |
#9
|
|||
|
|||
oops
-- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... 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. -- HTH Bob Phillips "Don Guillett" wrote in message ... or try with { } =SUMPRODUCT(((A2:A20={"Apartment","House"})*(B2:B2 0={"San Francisco","New York"})*C2:C20) -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20) for one way -- HTH Bob Phillips "Alejandro" wrote in message ... 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 |
#10
|
|||
|
|||
He may not have meant it that way, but he did say it <g
Bob "Don Guillett" wrote in message ... oops -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... 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. -- HTH Bob Phillips "Don Guillett" wrote in message ... or try with { } =SUMPRODUCT(((A2:A20={"Apartment","House"})*(B2:B2 0={"San Francisco","New York"})*C2:C20) -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =SUMPRODUCT(((A2:A20="Apartment")*(B2:B20="San Francisco")+(A2:A20="House")*(B2:B20="New York")),C2:C20) for one way -- HTH Bob Phillips "Alejandro" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for multiple conditions | Excel Discussion (Misc queries) | |||
Conditional formatting: I have five conditions, how to do this? | Excel Discussion (Misc queries) | |||
SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
should be possible to add more conditions to conditional formatti. | Excel Discussion (Misc queries) | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |