Sum Column Based on Cell Color

[Thomas M.]

Excel 2007

Is there a way to sum a column of numbers based on the background color of
the cells? For example, I have a column that contains gold, blue, and green
cells, and I want to sum only the green cells. The color definition is:

Red 153
Green 204
Blue 0

 

[Don Guillett]

Sum based on the same criteria as how the cells became colored.

 

[Thomas M.]

The cells were colored manually. Each cell is a grand total for a
corresponding table. I figure that I can sum only the grand total numbers
from each table by either reworking the spreadsheet and using Excel's
subtotal feature, or by adding a column that would include only those grand
total numbers. But when I was looking at the problem I had this idea of
doing a conditional sum based on the cell color, and I just got curious as to
whether or not that can be done.
--
Thanks for any help that you can offer!

--Tom

Sum based on the same criteria as how the cells became colored.

 

[Pete_UK]

Bob Phillips shows ways of doing it here:

http://www.xldynamic.com/source/xld.ColourCounter.html

Hope this helps.

Pete

 

[Herbert Seidenberg]

Excel 2007
Identify all 256^3 RGB colors,
not just the 56 index colors
http://www.mediafire.com/file/zmqlnzkmnwz/09_01_09.xlsm

 

[pshepard]

Hi Thomas,

If you create the following User Defined Function (UDF), by entering Alt + F11

paste the following code into a Module:

Function Color(cell)
Color = cell.Interior.Color
End Function

Column with colored cells A2:A100, insert a helper column into column B,
then in cell B2:

=color(A2)

copy the formula from B2 to B3:B100.

=sumif(B2:B100,B2,A2:A100)

note, the color number for Red 153, Green 204, Blue 0 is 52377

so your formula could be:
=sumif(B2:B100,52377,A2:A100)

OR

You can also use the autofilter to filter by color. When data is filtered,
then you can use the subtotal worksheet function:

=SUBTOTAL(9,A:A)