SUMIF formula where the colour of the cell is the criteria

[benmormedical]

I have created a worksheet with a list of quotes that are either outstanding
or have produced a sale and I am using the good, bad and neutral styles to
distinguish which is which, eg. a quote that has resulted in a sale is
highlighted as 'good' (light green shading with a dark green font).
I want a cell at the top of the sheet that adds all of the outstanding
quotes together to show how much possible revenue is outstanding, I have
tried using the SUMIF function but I dont know how to describe the cells when
writing the formula so that it only adds the outstanding ones.

help? :S

 

[Otto Moehrbach]

A formula cannot work with the color of the cell/font. VBA is needed for
that. However, a formula can work with the conditions that drive the
colors. Post back with more detail about those conditions. HTH Otto

 

[benmormedical]

Ok, there is a tab at the top called 'Styles' (I am using excel 2007) where
you can change the style of the cell to highlight different things and three
of the styles are Good, Bad and Neutral
Clicking 'Good' changes the fill of the cell to a pale green and the font to
a dark green, Bad- pale red fill, dark red font and Neutral is a pale yellow
fill with dark orange font. Can I maybe use the style as the criteria ie.
'style=Good' or something along those lines?

p.s. thank you for the quick response

 

[Teethless mama]

Assuming your data in in A1:A10 and a header row in row 1

In C1: =SUBTOTAL(9,A2:A10)

Home Tab > Auto Filter > Filter > select Filter by color

 

[Herbert Seidenberg]

Excel 2007 Tables
CF with Icons
No formulas, no code
http://www.mediafire.com/file/uztytmgdzrn/08_03_09.xlsx

 

[bctx]

I have created a worksheet with a list of quotes that are either outstanding
or have produced a sale and I am using the good, bad and neutral styles to
distinguish which is which, eg. a quote that has resulted in a sale is
highlighted as 'good' (light green shading with a dark green font).
I want a cell at the top of the sheet that adds all of the outstanding
quotes together to show how much possible revenue is outstanding, I have
tried using the SUMIF function but I dont know how to describe the cells when
writing the formula so that it only adds the outstanding ones.

help? :S

 

[bctx]

One way summarize data that has been higlighted is to add a column in the
worksheet. Then use copy - past special to copy the formatting only to the
new blank column from one of the columns that has highlighted data.

Then use Find & Rreplace with Opitons to search the new column for the cells
with the color, then replace with the same formatting, but with the word
added that applies to the color, i.e., "good". You will have to do these for
each color used to higlight the data. Shouldn't take long if you only have
three conditional colors.

You will now have a column that can be used to sumif based on the name,
i.e., "good". Also, you can use the column to pivot the data.