Counting number of adjacent conditional formatted cells by color

Question

I've a file in which some cells have been red colored after using conditional formatting across the columns. Now, I would like to count the number of red colored cells across the rows. An example of my Excel sheet is given below:

The following results are expected:

Row 1: 1
Row 2: 1
Row 3: 0
Row 4: 0
Row 5: 3

Any ideas?

Answer 1

It doesn't seem possible using a built-in Excel formula, however this site points to a VBA solution that would probably work for you:

http://www.xldynamic.com/source/xld.CFConditions.html

He does a nice job of explaining the difficulties faced and building several VBA functions that work together to be able to count the number of cells that have their conditional formatting triggered.

Answer 2

Couldn't think of a magic formula to do it, but a trick that will work for Excel 2007 and later.

You could COPY it to a new sheet, and when pasting, use the special Transpose Paste. (this will turn your rows into columns and vice versa).

Then you can enable the Filter, and filter by color.

It does require some manual work, but it will get the job done.

p.s. before copy-pasting, add in column A row numbers, so you won't get lost later on.