COUNT CELLS WITH CONDITIONAL FORMATTING

[Guest]

Hi. I must have missed a function somewhere. I have a range of numbers that
have conditional formatting based on matching an input value. What I would
like to do is count the cells that are conditionally formatted. I tried the
count functions but cannot find an argument to identify formatting
differences. TIA -- Phil

 

[Rick Rothstein \(MVP - VB\)]

Hi. I must have missed a function somewhere. I have a range of numbers

that
have conditional formatting based on matching an input value. What I would
like to do is count the cells that are conditionally formatted. I tried
the
count functions but cannot find an argument to identify formatting
differences.

Why not use the same condition that you used in the conditional format in a
SUMPRODUCT to count the cells. For example, if your conditional format is
based on cells, say for example, A1:A200 matching an input value in, say,
B1, then use this...

=SUMPRODUCT(--(A1:A200=B1))

or, depending on what your conditional actually is, something similar to the
above. The point is, you don't have to search the range for the conditional
formatting you set, just search for the cells that match the conditions you
set in the Conditional Formatting... if that condition was sufficient to set
the format, then it should be sufficient to be counted.

Rick

 

[T. Valko]

Don't count based on formatting. Count based on: "matching an input value."

But, we'd need the details!

 

[Guest]

Ok,

a1:a6 are input. Six numbers (1,2,3,4,5,6)

b1:b6 is input from another range. (1,7,9,15,17,21,34,67,89)

I need to count the matches. The obvious answer is 1 in this example.

It's on the tip of my brain, teetering, just waiting to fall into place.

 

[T. Valko]

Ok, so which range do you want to count against? A1:A6 or B1:B6?

This will count matches in A1:A6 that appear in B1:B6 :

=SUMPRODUCT(COUNTIF(B1:B6,A1:A6))

If I have it backwards just flip the ranges.

 

[Guest]

I would bet a weeks pay that I tried that formula or a very close varient of
it last night before posting the question. When I noticed your answer I was
working on a "count unique occurances" formula that would have subtracted
them from the total numbers. Ugly, Clunky, etc. Thank you very much for
saving my workbook from ugly and clunky.

-- Phil

 

[T. Valko]

You're welcome. Thanks for the feedback!