Array Formula w/ Multiple SumIf Criteria

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" <[email protected]> wrote in message
news:[email protected]...
> 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%
>
>
>

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%
>
>
>

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

--
HTH

Bob Phillips

"Dave Breitenbach" <[email protected]> wrote in
message news:[email protected]...
> 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%
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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%
>
>
>

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%
>
>
>

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

--
HTH

Bob Phillips

"Dave Breitenbach" <[email protected]> wrote in
message news:[email protected]...
> 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%
> >
> >
> >

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%

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" <[email protected]> wrote in message
news:[email protected]...
> 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%
>
>
>

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%
>
>
>

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

--
HTH

Bob Phillips

"Dave Breitenbach" <[email protected]> wrote in
message news:[email protected]...
> 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%
> >
> >
> >