Using function based on "Font Color"
 

Could you please inform me if there is a function which can count and/or
summarize cells in excel based on "Font Color"

Using function based on "Font Color"
 

Function CountCells(rng As Range, ci As Long)

For each cell in rng

If cell.Font.Colorindex = ci then

CountCells = CountCells +1
End If
Next cell
End Function

=CountCells(A1:B10,3)

--
__________________________________
HTH

Bob

"danai_mp" wrote in message
...
Could you please inform me if there is a function which can count and/or
summarize cells in excel based on "Font Color"

Using function based on "Font Color"
 

Thank you very much for your answer but unfortunately I am not so good in this
I need more help
If I have A1 = 7 (red colour), A2 =5 (green colour), A3 = 4 (lime colour),
A4 = 2 (green colour), A5 =6 (red colour), A6 =2 (red colour) etc
what can I use to have (a) the sum of red colour cells and (b) the count of
green colour cells?
thank you
--
Best regards
danai_mp


Ο χÏ�ήστης "Bob Phillips" Î*γγÏ�αψε:

Function CountCells(rng As Range, ci As Long)

For each cell in rng

If cell.Font.Colorindex = ci then

CountCells = CountCells +1
End If
Next cell
End Function

=CountCells(A1:B10,3)

--
__________________________________
HTH

Bob

"danai_mp" wrote in message
...
Could you please inform me if there is a function which can count and/or
summarize cells in excel based on "Font Color"

Using function based on "Font Color"
 

Here is a slightly different way.

Add this function

Function CellColours(rng As Range) As Variant
Dim ary As Variant
Dim cell As Range
Dim i As Long

ReDim ary(1 To rng.Rows.Count)
i = 1
For Each cell In rng

ary(i) = cell.Font.ColorIndex
i = i + 1
Next cell
CellColours = ary
End Function

and then sum like

=SUMPRODUCT(--(CellColours(A1:A10)=3) ,A1:A10)

and simply count like

=SUMPRODUCT(--(CellColours(A1:A10)=10))

--
__________________________________
HTH

Bob

"danai_mp" wrote in message
...
Thank you very much for your answer but unfortunately I am not so good in
this
I need more help
If I have A1 = 7 (red colour), A2 =5 (green colour), A3 = 4 (lime colour),
A4 = 2 (green colour), A5 =6 (red colour), A6 =2 (red colour) etc
what can I use to have (a) the sum of red colour cells and (b) the count
of
green colour cells?
thank you
--
Best regards
danai_mp


? ??????? "Bob Phillips" ???????:

Function CountCells(rng As Range, ci As Long)

For each cell in rng

If cell.Font.Colorindex = ci then

CountCells = CountCells +1
End If
Next cell
End Function

=CountCells(A1:B10,3)

--
__________________________________
HTH

Bob

"danai_mp" wrote in message
...
Could you please inform me if there is a function which can count
and/or
summarize cells in excel based on "Font Color"