Sum if Cells Highlighted Particular Colour

**uberchick** · 09-30-2008, 11:17 AM

I know I've written a formula like this ages ago, but I can't seem to recall.
I want to write a formula that says to look at a table of cells, and to sum all cells that are highlighted a certain colour. Does anyone know how to do this? Thanks in advance!

**shg** · 09-30-2008, 11:20 AM

Welcome to the forum, uberchick, you've been lurking a long time.

This requires VBA. See http://www.cpearson.com/excel/colors.aspx.

**uberchick** · 09-30-2008, 11:32 AM

Thanks for the link... unfortunately, my VBA knowledge is almost non-existent. I'm pretty sure I didn't use VBA to do this before... is there another way?
Thanks in advance!

**shg** · 09-30-2008, 11:35 AM

If you remember how you did it without VBA, you will earn my praise and admiration.

**EdMac** · 09-30-2008, 11:39 AM

The only way you could do it without VBA is if the cells are coloured using conditional formatting.

If this is the case, then you could sum them using the same rule as is used to colour them.

**uberchick** · 09-30-2008, 11:51 AM

Unfortunately, the coloured cells are not conditionally formatted so this won't work. Thanks anyway!

**shg** · 09-30-2008, 12:09 PM

Equivalent to EdMac's suggestion, if the cells are colored according to some rule, then you can put a formula that encodes the rule in another column, and use that to conditionally sum the colored cells.

There's potential for error if the cell colors don't allows follow the rule, though.