COUNTIF a specific coloured text

[glad_ir]

Hello All,

Any help with this one would be much appreciated please.

Does anybody know if it is possible to count cells containing text of a specific colour? I have a column with a range of text entries that are coloured green, amber and red. The text entries are variable but the shades of green, amber and red are controlled. I would like to separately sum the green, amber and red text entries.

Thank you,
Iain

 

[Peter_SSs]

I would like to separately sum the green, amber and red text entries.

Do you mean count the green, amber & red text entries?

This would require vba. Is that acceptable?
If so, are the green/amber/red colours applied manually, by conditional formatting, by vba code, some other way?

 

[glad_ir]

Do you mean count the green, amber & red text entries?

This would require vba. Is that acceptable?
If so, are the green/amber/red colours applied manually, by conditional formatting, by vba code, some other way?

Hi Peter,

Thank you for the reply. VBA is acceptable. The colours will be applied manually rather than by other means.

Iain

 

[Peter_SSs]

Could you use something like this then? The text colour in D2:D4 is used to find matches for that colour in column B.
In my sample, cell B5 is formatted as Red font but is excluded from the count since there is no text in that cell. Is that what you would want?

Post back if you need help implementing this user-defined function.

Function CountC(rng As Range, clrcell As Range) As Long
  Dim c As Range
  Dim clr As Long
  
  clr = clrcell.Font.Color
  For Each c In rng
    If c.Font.Color = clr And Len(c.Value) > 0 Then CountC = CountC + 1
  Next c
End Function

glad_ir.xlsm
	B	C	D	E
1
2	text 1		green	3
3	text 2		amber	0
4	text 3		red	4
5
6	text 5
7	text 6
8	464
9
10	text 9
11	text 10
12	text 11
13	text 12
14	text 13
15	text 14
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=CountC(B$2:B$15,D2)

 

[glad_ir]

Could you use something like this then? The text colour in D2:D4 is used to find matches for that colour in column B.
In my sample, cell B5 is formatted as Red font but is excluded from the count since there is no text in that cell. Is that what you would want?

Post back if you need help implementing this user-defined function.

Function CountC(rng As Range, clrcell As Range) As Long
  Dim c As Range
  Dim clr As Long
 
  clr = clrcell.Font.Color
  For Each c In rng
    If c.Font.Color = clr And Len(c.Value) > 0 Then CountC = CountC + 1
  Next c
End Function

glad_ir.xlsm
	B	C	D	E
1
2	text 1		green	3
3	text 2		amber	0
4	text 3		red	4
5
6	text 5
7	text 6
8	464
9
10	text 9
11	text 10
12	text 11
13	text 12
14	text 13
15	text 14
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=CountC(B$2:B$15,D2)

Thank you....I will give this a try now and come back to let you know how I get on. Thanks again

 

[glad_ir]

Hi, this is perfect. I've only used VBA for macros rather than functions before so a neat learning for me here as well!

Thank you for your help. Have a great xmas!

Iain

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Have a great xmas!

Same to you!