Array Formula w/ Multiple SumIf Criteria

[Andy]

Hello!
I'm trying to find a more elegant way to accomplish a conditional sum and
formula with multiple conditions.

Sample data is below. Column A is Product, range named Product. Column B
is Location, range named Location. Column C is Total number, range named
TotalNum. Column D is number in stock, range named Stock. Column E is
Number sold, range named Sold. Column F is % Sold, range named Percentage
with the simple formula being (D1-E1)/D1 for % Sold.

I want to find % Sold for multiple conditions. As in example, % Sold of
Toasters in New York.

Here is the formula I have now, but it seems overly cumbersome using two
conditional statements that repeat the same criteria.

=SUM(IF(Product="Toaster",IF(Location="New
York",Sold,0),0))/SUM(IF(Product="Toaster",IF(Location="New
York",TotalNum,0),0))

Answer for New York Toasters = 71% is correct.

I tried the following, but it only sums incorrectly:

=SUM(IF(Product="Toaster",IF(Location="New York",Percentage,0),0))

Answer for New York Toasters = 130% is incorrect.

I am sure that with array formulas that there must be an easier way to do
this, but can't seem to figure it out. I was also thinking of using a
drop-down control for user selects with a macro returning the answer based on
selections.

Thanks for any assistance!!!!

Product Location Total Sold Completed Percentage
Toaster New York 4 2 2 50%
Oven Philly 17 0 17 100%
Toaster New York 10 2 8 80%
Microwave Pittsburgh 10 5 5 50%
Microwave Toledo 5 0 5 100%
Sink New York 4 1 3 75%
Toaster Philly 4 1 3 75%
Toaster Philly 87 15 72 83%
Oven Pittsburgh 9 8 1 11%
Sink Toledo 10 5 5 50%

 

[Bob Phillips]

Don't think you can avoid.but you can make the formula a wee bit shorter

=SUM(IF((Product="Toaster")*(Location="New
York"),sold,0))/SUM(IF((Product="Toaster")*(Location="New
York"),totalnum,0))

 

[Dave Breitenbach]

I renamed your completed range as "completed" and used this formula...
I dont see the number in stock column you were referring to, so my formula
assumes that total - sold = completed

=SUMPRODUCT(--(product="Toaster"),--(location="newyork"),--(completed))/SUMPRODUCT(--(product="Toaster"),--(location="newyork"),--(totalnum))


hth,
Dave

 

[Bob Phillips]

he was referring to completed - I had the same problem.