Formula to count colours (or symbols) in Excel?

[AnnN]

I need some help to count the number of red cells in a row (or the number of'<' symbols). When I use a countif function it returns zero so there must another function suitable and I am having a mental block. Anyone able to help please? I have attached an image of the spreadsheet that I am trying to count. I don't have the developer link so I am unable to use VB at this time.

 

Many thanks,

Ann

 

[Steve21]

=COUNTIF(A1:A4,"~<*")

etc

 

Steve

[MrMat]

I need some help to count the number of red cells in a row (or the number of'<' symbols). When I use a countif function it returns zero so there must another function suitable and I am having a mental block. Anyone able to help please? I have attached an image of the spreadsheet that I am trying to count. I don't have the developer link so I am unable to use VB at this time.

 

Many thanks,

Ann

 

[ATTACH=CONFIG]31155[/ATTACH]

 

 

Try inserting * inside of your "" when doing the Countif i.e. =countif(A1:A50,"*>*")

[AnnN]

THANK YOU VERY MUCH!

I knew there would be a simple solution and that has just made my day.

Thanks again,

Ann

[LosOjos]

In the interests of completeness, I wrote a couple of functions that enable you to count cells based on their colour a while back (though I advise you avoid using macros whenever possible; the methods above are a better fit in your case):

 

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

 

Usage: pick a cell that is the same colour as that which you wish to count (I usually put a key somewhere to help here). Say that cell's address is 'Sheet2!A1' and the range you want to count is 'Sheet1!A2:A100', the formula you would use on your sheet would be:

 

=COUNT_COLOUR(Sheet1!A2:A100, CELL_COLOUR(Sheet2!A1))

[MrMat]

In the interests of completeness, I wrote a couple of functions that enable you to count cells based on their colour a while back (though I advise you avoid using macros whenever possible; the methods above are a better fit in your case):

 

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

 

Usage: pick a cell that is the same colour as that which you wish to count (I usually put a key somewhere to help here). Say that cell's address is 'Sheet2!A1' and the range you want to count is 'Sheet1!A2:A100', the formula you would use on your sheet would be:

 

=COUNT_COLOUR(Sheet1!A2:A100, CELL_COLOUR(Sheet2!A1))

 

Show off...