Count Cells Based on Colour Determined by Conditional Formatting

Hi all,

I've tried many things on this one, and a colleague has directed me to this forum with high praise for what you're able to do. It's my last resort; so here's hoping!

I'm trying to get a way to count the amount of cells coloured green in a document, but the cells are coloured by conditional formatting (two rules - format top one [green] and bottom one [red] based on range).

I've found a few options via Google, but none seem to work right. I'll say right as I've been able to place a condition that if it doesn't find the colour is displays "Colour not found" (which works), but when it should find the colour it's returning a #Value error.

Things to note:
• I've attached a dummy document - This document shows only one table on Sheet 1 - In the real document, there are multiple.
• Cells are not coloured based on a specific value, but rather the highest one. The highest number could vary from 0 to 25.
• The conditional formatting is spread across the row, aimed to highlight the Group with the highest score for that row/criteria (along with highlighting the lowest in red).
• The aim is to count how many Greens a column has - Essentially, how many of the top scores has the column got. In this example document, Group 1 has ten, Group 2 has three, Group 3 has three and Group 4 has four.
• The information is pulled into the chart from different sheets.

I've been able to implement a really rough work-a-round in my main document by chucking in a =MAX cell at the end of each row, chucking an =IF to assign "1" when it matches the max and "0" when it doesnt, and then =COUNTIF on the range for "1". However, this isn't the tidiest solution, so any help to aid in tidying this one up would be appreciated.

Thanks.

you could build this function of the colors you want to use:
then an outer cell would be F3=getColor(a3)
to convert the color to english, then you can pivot data.

Please Login or Register  to view this content.