Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Formula to Exclude Certain Cell Values/Backgrounds
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Formula to Exclude Certain Cell Values/Backgrounds
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. Phil Hageman wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need this formula to exclude cell value 'x' and 'xx' when counting | Excel Discussion (Misc queries) | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
charts exclude cell with formula | Charts and Charting in Excel | |||
funtions for colors for fonts or cell backgrounds? | Excel Worksheet Functions | |||
Excel 2003 - SUM Formula - exclude cell | Excel Worksheet Functions |