Counting coloured cells in MS Excel 2010

[QWERTY72]

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

[bladedanny]

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.

[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.

[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

[LosOjos]

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.

 

To count "=":
=COUNTIF(RANGE,"==")

To count "+":
=COUNTIF(RANGE,"=+")

To count "-":
=COUNTIF(RANGE,="=-")

[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.

[LosOjos]

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:

 

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:

 

=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)

[QWERTY72]

This works fine, thank you! But I will try the colour counting as well - been curious about it for quite some time.