Count cells of a certain colour to give a percentage

[Lmaonade]

Hi,

I don't know if this is possible but please see my screenshot below of an example I've created.

I want the cell in B2 to return the % of all the cells from C2:X2 that are the green colour, these cells are green as a result of conditional formatting in the vlookup table. So B2 should return 16.66% as 3/18 of the cells are green. And B3 would return 50% as half the cells are green. Is there some formula I can use to count cells of a certain colour?

Thanks,
Andy

 

[Joe4]

You cannot do native Excel formulas that are based on formatting, such as the color of cells.
However, since those cells are colored by Conditional Formatting, you can use the condition (formulas) that you used for Conditional Formatting in the formula for cell B2 (using COUNTIF or COUNTIFS, then dividing by the total number of cells you are including, which looks like 18).

 

[Drexl88]

Hi, I require the same solution. How do you incorporate the condition (formulas) into the formula?

Thanks

 

[Joe4]

What are your Conditional Formatting rules?
And what range do you want to apply this to?

 

[Drexl88]

Hi,

Multiple ranges as below;

F4:R7, F9:R17, F189:R55, F57:R58, F60:R66

Conditional Formatting rules are;

=TODAY()-G4<45 (Green)
=TODAY()-G4<=60 (Amber)
=TODAY()-G4>60 (Red)
Specific text ="LTA" (Blue)
Specific text ="LTS" (Yellow)


Any help would be much appriciated.

 

[Joe4]

Are those Conditional Formatting Rules applied to every column F - R, or just certain ones?
And are you just trying to get the Count for each color?

 

[Drexl88]

Yes, applies to each column. Yes, so I can calculate a percentage of each from total number of applicable employees.

Hope that makes sense.

 

[Joe4]

Well, your specific text ones are pretty easy.

For the first two ranges, the formula would be:
=COUNTIF(F4:R7,"LTA") + COUNTIF(F9:R17,"LTA") + ...
and just continue on, doing a COUNTIF for each range and adding them all together to get the total count.

For the other ones, I am a bit perplexed. Without seeing your data, I am afraid it is not making much sense.
Would you be able to post what the first two ranges look like, so we can see data in these cells and their colors?

 

[Drexl88]

Of course, see below. I am counting them to see how many employees have up to date training attendance. I cant count them by the number of dates that fall within the compliance timeframe as some will attend earlier than required. Therefore both dates would be counted and would give an error in the percentage. Hence wanting to count how many are green/amber as there is another CF that turns the earlier date blank when a new date is added. Sorry if I have not been clear.

 

[Joe4]

OK, in the first block, can you walk me through the reasons why 10/06/2020 is red?
What cell is it in (cell address)?
What cells is that particular one looking at for the Conditional Formatting?