Counting Conditional Formats

**droddis** · 06-10-2008, 02:00 PM

I read this thread but it seems to refer to counting only one type of format. I have multiple columns in my sheet, with multiple formats (The variables change for each column though still only return 1 of 2 colours.) but want to count how many of each resulting colour there are across the whole range.

The sheet is an overview of sales accounts we "own" for various brands in various verticals. So Grey= our account and Green= Competitor or unknown. Is there a way to count all the Grey and Green accounts?

that way I can make a % of what penetration we have by territory, brand, and market segment.

All help appreciated.

**NBVC** · 06-10-2008, 02:28 PM

You probably just need a Countif() formula

e.g. =Countif(A1:A100,"our_account") for Grey

and =Countif(A1:A100,<>"our_account") for Green

adjust ranges to suit and and change "our_account" to your actual account name (leave the double quotes in tact).

**droddis** · 06-10-2008, 02:36 PM

I was hoping there was a way I could just count the green vs grey as opposed to having to write a new formula based on the variables that turned the cell green or grey. I have a number of columns with multiple variables.

Do you know if it's posisble to count solely on the colour of the cell derived from a conditional format?

Thanks

David

**NBVC** · 06-10-2008, 02:41 PM

You could combine the formulas

e.g. =Countif(A1:A100,"our_account")/(A1:A100,<>"our_account") or other ways...

If you can say exactly what you want the formula to do, then we can help further.

**droddis** · 06-10-2008, 02:47 PM

I'm struggling with nomenclature!

The easiest way to say it is that the only thing in common that the green cells or the grey cells will have is that they are green or grey. The conditional format that made them that colour will vary by each column, virtually no column has the same variables that would return a positive (grey) or a negative (Green). As a result I was hoping to count only based on the colour of the cell as opposed to the content.

**shg** · 06-10-2008, 02:47 PM

Counting the results of conditional formatting requires VBA, and is complicated well beyond its value. If you want to do it anyway, Chip Pearson has code on his site.

It's much simpler to use a separate column to compute conditional formatting formulas, and then use that for both conditional formatting and anything you want to measure.