Working with formulas
 

I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7. I
would want to divide 7 into 1 for a percent. I have been able to accumulate
the columns but not get a percent. This is the formula that I have so far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white

Working with formulas
 

Daniell,

I may not be understanding you fully but try:

=SUMPRODUCT(($I$1:$I$99="Green")*($A$1:$A$99="Stoc k")*(B1:B99))/SUM(B1:B99)

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Daniell" wrote in message
...
I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the
total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7.
I
would want to divide 7 into 1 for a percent. I have been able to
accumulate
the columns but not get a percent. This is the formula that I have so
far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white

Working with formulas
 

Daniell,

I order to calculate based on the colors of a cell, you have to create your
own UDF (user-defined function).
See here for details: http://www.cpearson.com/excel/colors.htm

--
Brevity is the soul of wit.


"Sandy Mann" wrote:

Daniell,

I may not be understanding you fully but try:

=SUMPRODUCT(($I$1:$I$99="Green")*($A$1:$A$99="Stoc k")*(B1:B99))/SUM(B1:B99)

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Daniell" wrote in message
...
I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the
total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7.
I
would want to divide 7 into 1 for a percent. I have been able to
accumulate
the columns but not get a percent. This is the formula that I have so
far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white

Working with formulas
 

Sandy, that is what I am attempting to do thank. Coloor coding the ceel then
checking the color might be something to look at.

"Dave F" wrote:

Daniell,

I order to calculate based on the colors of a cell, you have to create your
own UDF (user-defined function).
See here for details: http://www.cpearson.com/excel/colors.htm

--
Brevity is the soul of wit.


"Sandy Mann" wrote:

Daniell,

I may not be understanding you fully but try:

=SUMPRODUCT(($I$1:$I$99="Green")*($A$1:$A$99="Stoc k")*(B1:B99))/SUM(B1:B99)

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Daniell" wrote in message
...
I am attempting to create a formual that would look at the color and
accumulate the numbers in each column and divide that total into the
total
for each column. For example for the color green(From Column I) and Issue
stock(column A) you have a total of 1and column Location 1 the total of 7.
I
would want to divide 7 into 1 for a percent. I have been able to
accumulate
the columns but not get a percent. This is the formula that I have so
far:
=SUM(IF((Sheet2!I2:I99="green")*(Sheet2!A2:A99="st ock"),Sheet2!B2:B99)) I
would repeat this formula in each of the cells I want a
percent.
issue Location 1 Location 2 Location 3 Location 4 Location 5 Color
mail 1 0 0 1 0 blue
order 0 1 0 1 1 blue
stock 0 0 1 0 0 blue
stock 1 0 1 1 0 green
mail 0 1 0 1 1 green
order 0 0 1 1 0 green
stock 1 0 0 1 1 orange
mail 0 1 1 1 1 orange
order 1 1 1 0 1 orange
order 0 0 0 1 0 red
stock 1 1 0 1 1 red
mail 1 0 1 0 0 red
order 0 0 0 1 1 white
stock 1 0 1 1 0 white
mail 0 1 1 0 1 white