Counting Colours

[Darren]

Hi, I have a group of cells, the colour of the cell is dependant on it's
value being either higher or lower than 'x'. The problem I have is that 'x'
isn't constant throughout the cells.

Example:
A1 = higher or lower than 10
A2 = higher or lower than 7
A3 = higher or lower than 25
etc.
These aren't the true values, just examples.

Higher than 'x' the cell is green, lower and it's red. What formula, if any,
do I put into another cell that can count the green or red cells in a given
range?

 

[Mike H]

Hi,

Alt+F11 to open Vb editor, right click 'this workbook' and insert module and
paste the code below in.

Call with =sumbycolor(A1:A20,B1)


Where A1:a20 is the range to count and B1 is the colour you want to count

Function SumByColor(Rng As Range, ClrRange As Range) As Double
Dim c As Range, TempSum As Double, ColorIndex As Integer
Application.Volatile
ColorIndex = ClrRange.Interior.ColorIndex
TempSum = 0
On Error Resume Next
For Each c In Rng
If c.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + 1
End If
Next c
On Error GoTo 0
Set c = Nothing
SumByColor = TempSum

End Function

Mike

 

[Mike H]

BTW this won't work with conditional formatted colours. That can be done but
it's more difficult.

Mike

 

[papou]

Hi Darren
Take it the other way round, ie:
How many cells have a value higher or lower than 10
How many cells have a value higher or lower than 7
....and so on

See help on COUNTIF or SUMPRODUCT functions.

HTH
Cordially
Pascal