Counting coloured cells that are conditionally formatted

Hi vmutch,

There appear to be various (complex) VBA solutions to this problem, e.g. this thread http://www.excelfox.com/forum/f22/ge...ng-or-not-338/ amongst others...

I'm not sure of your exact criteria, but I wondered if this would achieve what you are looking for (see attached). The criteria can be adjusted to suit your needs.

I've added CF to each column to change the cell colour and to keep it simple assumed that the cell will turn red when the cell value in the first column is greater than 1, in the second column when the cell value is greater than 2, in the third column when the cell value is greater than 3 and so on, but any criteria will work...

I've then used an array formula to count the number of red cells in the range C7:O7 (on the attached sample) and CF to change the colour of cell Q7 to a specified colour (pink in the sample) when five or more cells in the range C7:O7 turn red. The number of red cells required to turn cell Q7 pink can simply be specified through the CF for cell Q7.

In Q7 I've added this formula as an array using Control + Shift + Enter.

Please Login or Register  to view this content.

Obviously adjust the range to suit. Let me know whether or not this achieves what you want?

If the colours are Condtionally Formatted, ie set by a condition then just count the conditions that are true which set the colour in the first place

Hi vmutch,

My formula in the previous post made the assumption that the cell will turn red when the cell value in the first column is greater than 1, in the second column when the cell value is greater than 2, in the third column when the cell value is greater than 3 and so on because I didn't know the criteria you'd used for your CF, however now that I know that, it makes it easy, you can simply replace the

Please Login or Register  to view this content.

with an array that matches your CF criteria to give you

Please Login or Register  to view this content.

where {0,1,3,3,3,9,8,0,0,4,0,0,2} matches the CF criteria for columns F:R to turn a cell red for each respective column. I've left column A with the same CF, so that when 5 or more cells turn red, the cell in column A turns 'Red', but just change this to suit your needs. I've also assumed, based on your sample that you are only including columns F:R in your calculation, despite the conditionally formatted cells running from C:R?


I've noticed that you have some duplication in your CF in some columns which you don't need, so I've removed the following

Column C - You format cells 'Green' both when > 2 and > 3, I've removed > 3
Column F - You format cells 'Red' both when > 0 and > 1, I've removed > 1. You also have two instances for formatting cells = '*', you can remove the second instance
Column G - You format cells 'Red' both when > 1 and > 2, I've removed > 2
Column J - You are formatting cells 'Light Red Fill with Dark Red Text' for both '*' and when the cell value is between 2 and 3, I've removed the entry for between 2 and 3 as that is covered by Yellow
Column K - You have three instances where you are formatting cells 'Light Red Fill with Dark Red Text' for '*', I've removed > 9 and the second '*'
Column K - You also have cells formatted as 'Red' when > 9 and > 10, I've removed > 10
Column L - You format cells 'Red' both when > 8 and ="8$M$6:$M$18", I've removed ="8$M$6:$M$18"
Column M - You have two instances where you are formatting '*' as 'Light Red Fill with Dark Red Text', I've removed the second instance
Column N - You have two instances where you are formatting '*' as 'Light Red Fill with Dark Red Text', I've removed the second instance
Column O - You have two instances where you are formatting '*' as 'Light Red Fill with Dark Red Text', I've removed the second instance

Note: on the attached sample, I've removed all the instances of CF for '*' and repalced it with a grey colour for blank cells.


The 'spanner in the works' is the addition of an '*' that you have included where you have blank cells. This causes an issue which I've not figured out a solution for yet because the '*'s are being counted as if they were numbers and I can't find a way to prevent that, however if the '*'s aren't precious and you would be happy to replace them with say a 'Dark Grey' cell instead (as I've done on the attached file) then that problem goes away and everything should be worknig now hopefully.