Count conditional format icons within a range

[PIJim]

Hello,

I need a formula to count icons within a range.
I have a large table of data. Each row has unrelated data for which i have created conditional formatting. They are either red lights or green lights.
I would like to count the number of each lights in the last column in the range which has values.

I hope this is clear. Any help would be greatly appreciated.

thanks
Jim

 

[James006]

Hello,

Counting icons ... why not ...

But are talking about shapes ... about conditional formatting ... about pictures ... ???

 

[PIJim]

Hello,

Counting icons ... why not ...

But are talking about shapes ... about conditional formatting ... about pictures ... ???

I am talking about conditional formatting icons.
Basically i have a file with 100+ rows of data broken into 7 categories (identified in a cell within each row. Each row has specific targets. Each subsequent column is a month which contains results for the month. A conditional format red light icon appears when the target is not achieved and a green one appears when the target is achieved. I would like to see how many achieved target and how many didn't for each of the categories.

 

[James006]

Hello again,

It seems to me ... rather than counting the icons resulting from your conditional formatting rules ...

You could use your underlying rules and count them to reach your objective ...

 

[PIJim]

how would i do that?

 

[James006]

What is the condition / formula you are actually using to produce your icons ...?

 

[PIJim]

That is exactly my problem. The condition is different throughout the table. In some cases it needs to be greater than or less than the target to be a green light, if not then red light. In other cases it needs to be between values to be green light, if not red light. In other cases it needs to be equal to or less than OR equal to or greater than.
Otherwise i could simply add a formula to count specific scenarios. The easiest way would be to count red and green lights if it is possible.
I want to avoid macros if possible since the workbook will be handed over to our Ops group.

thanks
Jim

 

[James006]

Counting Font Colors ... Counting Interior Colors ... is very simple ...

BUT Counting Colors resulting from Conditional Formatting is a serious headache ...

If you really want to dig into this topic ... Chip has designed a solution :

http://cpearson.com/excel/CFColors.htm

Hope this will help

 

[PIJim]

Thanks

I was able to find a way without counting the conditional formatting icons. I nested a bunch of IF and INDEX statements.

 

[James006]

Glad you could fix your problem