Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array Formula w/ Multiple SumIf Criteria
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% |
#2
|
|||
|
|||
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)) -- HTH Bob Phillips "Andy" wrote in message ... 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% |
#3
|
|||
|
|||
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 "Andy" wrote: 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% |
#4
|
|||
|
|||
he was referring to completed - I had the same problem.
-- HTH Bob Phillips "Dave Breitenbach" wrote in message ... 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))/SUMP RODUCT(--(product="Toaster"),--(location="newyork"),--(totalnum)) hth, Dave "Andy" wrote: 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% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Modify SumIF... Array Formula | Excel Worksheet Functions | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |