Sum Formula to Exclude Certain Cell Values/Backgrounds

[Guest]

Incell range W15:W31, percent values are font/background-color conditional
formatted.

In cell W10 is the following formula: =SUM(X15:X31)

What would the formula be to exclude in the sum, a cell having a green
background and a zero percent value?

 

[Dave Peterson]

First, there's not worksheet formula that looks at the colors used in the cells.

You could write your own UserDefinedFunction that looks at colors. Finding the
colors that are applied via Format|cells is straightforward. But finding colors
based on the conditional formatting rules is not for the weak at heart.

Chip Pearson has some code for the format|cells stuff at:
http://www.cpearson.com/excel/colors.htm
look for: Summing The Values Of Cells With A Specific Color

and he has some hellacious code for just finding the conditional formatting
stuff at:
http://www.cpearson.com/excel/CFColors.htm

=====
But if the value is 0, no matter what the color, it shouldn't affect the =sum()
formula.

0 + prettymuchanything = prettymuchanything.