How do I count colored cells in Excel?

Question

I want to write a formula that will count the number of cells in a row which I've formatted green. How do I do this, in Excel 2000? Thanks!

Answer 1

There is no way to do this from a formula, at least that I know of. You could accomplish through VBA, however, with a macro that tested the color index of each cell and counted accordingly, but only if you are comfortable with VBA. You could also create a custom function in VBA that would accomplish.

Answer 2

I would suggest that instead of formatting each cell manually, you establish criteria for Conditional formatting. In that circumstance, you can have a countif formula set up on the same criteria and get to your answer.

Are your formatted cells valued, or blank? If they are blank, then consider valuing them with the number 1, and format both the text and fill to the same color. Then, you won't be able to see the actual number, but a simple sum formula will be able to count.

Alternatively, instead of the number 1, consider typing "YELLOW" or "BLUE" in the cell, then doing a countif(range,"YELLOW") to get to your answer.  The same color text & fill could also apply in this scenario.