Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Can you use the same condition for the conditional formatting in a COUNTIF or something similar? – Excellll Oct 11 '12 at 18:24
@Excellll, unfortunately not. I got the file from someone else and I don't know which the condition was. – Tin Oct 12 '12 at 7:18
Have you tried looking under Conditional Formatting >> Manage Rules...? If the formatting is applied conditionally, there should be a rule there. If the formatting was applied some other way (by VBA perhaps), you can use something like the solution found in this post: – Excellll Oct 12 '12 at 13:22

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:

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.

share|improve this answer

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.

Using transpose and then 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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .