# Counting coloured cells in MS Excel 2010

• 4th March 2013, 04:01 PM
QWERTY72
Counting coloured cells in MS Excel 2010
Hi
I was wondering if anybody could give me a helping hand please. I am exporting data from our SIMS into Excel and rather than counting *, =, +, - etc. I would like to count the coloured background of those cells. I can filter the data by the coloured background, but I don't know how to count them (other than manually, obviously).
Grateful for any help!
Thanks
• 4th March 2013, 04:06 PM
If it's only a quick count, after filtering if you highlight all the cells it should give a summary of stats including count.

If you want it permanently some form of COUNTIF() could be used.
• 4th March 2013, 04:12 PM
QWERTY72
I was going for the COUNTIF, but when when I tell Excel to look for a = (equal sign) within the designated range, it won't accept that. Now if I could tell Excel to count the number of red coloured cells, that would solve my problem.
• 4th March 2013, 04:14 PM
VeryPC_Tom_M
the only real way is to get VBA involved..

Here's a website with a bunch of colour functions for Excel 2003 - Color Functions In Excel
• 4th March 2013, 04:19 PM
LosOjos
Quote:

Originally Posted by QWERTY72
I was going for the COUNTIF, but when when I tell Excel to look for a = (equal sign) within the designated range, it won't accept that. Now if I could tell Excel to count the number of red coloured cells, that would solve my problem.

Don't overcomplicate things, count the symbols if you have them.

Code:

```To count "=": =COUNTIF(RANGE,"==") To count "+": =COUNTIF(RANGE,"=+") To count "-": =COUNTIF(RANGE,="=-")```
• 4th March 2013, 04:20 PM
QWERTY72
I don't know where to start with VBA - it looks terribly complicated.I'm just using the standard colours red, yellow, dark and light green.
• 4th March 2013, 04:31 PM
LosOjos
Quote:

Originally Posted by QWERTY72
I don't know where to start with VBA - it looks terribly complicated.I'm just using the standard colours red, yellow, dark and light green.

If you must count by colour (and I don't recommend it, see my previous post for Excel formulas that will count the symbols), then you'll want to add this code to a module in your workbook:

Code:

```Public Function cell_colour(Cell_Check As Range) As Long cell_colour = Cell_Check.Interior.Color End Function Public Function count_colour(Count_Range As Range, Colour As Long) As Long Dim x As Range count_colour = 0 For Each x In Count_Range     If x.Interior.Color = Colour Then         count_colour = count_colour + 1     End If Next x End Function```
Then you can use those functions in your worksheet like any other excel function, like so:

Code:

`=COUNT_COLOUR(RANGE, CELL_COLOUR(CELL))`
The range is the range of cells to count, and using the cell_colour function and highlighting a cell that is the same colour as that which you wish to count will get the colour code (simpler than working it out every time)
• 4th March 2013, 05:45 PM
QWERTY72
This works fine, thank you! But I will try the colour counting as well - been curious about it for quite some time.