Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

http://i.imgur.com/InrUQ.png

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: superuser.com/q/328326/76571 –  Excellll Oct 12 '12 at 13:22
add comment

2 Answers 2

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.

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.