Count conditional format icons within a range

PIJim

Board Regular
Joined
Jul 29, 2005
Messages
143
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Counting icons ... why not ...

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

PIJim

Board Regular
Joined
Jul 29, 2005
Messages
143
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Board Regular
Joined
Jul 29, 2005
Messages
143

ADVERTISEMENT

how would i do that?
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
What is the condition / formula you are actually using to produce your icons ...?
 

PIJim

Board Regular
Joined
Jul 29, 2005
Messages
143

ADVERTISEMENT

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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Board Regular
Joined
Jul 29, 2005
Messages
143
Thanks

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

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top