Working with formulas

[Guest]

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="stock"),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

 

[Sandy Mann]

Daniell,

I may not be understanding you fully but try:

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

and format the cell as percentage.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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

 

[Guest]

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