Excel 2007 : Counting cells based on their colour

**fithawk** · 10-30-2011, 05:54 PM

Hi.

In the attached workbook I am manually adding 1 for Green, 2 for Yellow and 3 for Red in column C, then COUNTIF to get totals. I would rather Excel recognise and count all cells with same colour i.e. all Green, all Yellow and all Red. Can excel count by colour? If so How is it done? Thanks.

**MarvinP** · 10-30-2011, 06:14 PM

Hi fithawk,
How about a User Defined Function (UDF) to do your counting. See the code in the example and the way it works.

Please Login or Register  to view this content.

This will count the number of cells in B5 to B end that are the color of the active cell that the formula is in.

**fithawk** · 10-30-2011, 06:25 PM

Originally Posted by MarvinP

Hi fithawk,
How about a User Defined Function (UDF) to do your counting. See the code in the example and the way it works.

Please Login or Register  to view this content.

This will count the number of cells in B5 to B end that are the color of the active cell that the formula is in.

No idea on macros, just a basic user. When I colour other rows the totals stay the same? Am I not doing something? Thanks

**MarvinP** · 10-30-2011, 06:37 PM

Hi,
You need to click in each of the colored cellls and put a space after the UDF and press enter. This will recalculate the count for each color.

The real answer is counting colors is very hard in Excel. I like your hidden column C better than trying to work with colors.

teylyn said it correctly when she said "colors aren't data"