dslreports logo
    All Forums Hot Topics Gallery
Search Topic:
share rss forum feed

·Optimum Online

[Excel] Count cells with a red background?

Is there a function that will return the count of all the cells in a spreadsheet (or range) that have a red background?

Sort of like COUNTIF(range,"Fill=Red")
NOTHING justifies terrorism. We don't negotiate with terrorists. Those that support terrorists are terrorists.

Premium,ExMod 1 BC
Not just yet.

Take a look at this web page. The basis for the solutions is what is known as a UDF (User Defined Function) - which is just a macro that can be called from a formula in a cell just like built in functions.

»www.xldynamic.com/source/xld.Col ··· ter.html

Chip Pearson also has a lot of information on counting with colors: »www.cpearson.com/excel/colors.as ··· ors.aspx

Generally depending on colors to get a count of something is not a great idea. Colors are generally thought of as just to make something look nice, or perhaps to bring notice of something to a viewer. But Excel has yet to be set up to count by color - although it keeps getting closer, since now you can filter data by color (and then get a SUBTOTAL() of the filtered results as noted on the one page).

And if it's conditional formatting colors you're trying to delve into - forget the colors, do the counting using the same type of test that you used to do the conditional formatting.
...then THINK! again.

I hate Vogons
Burlington, ON
reply to jaa
You can filter by color in excel 2010 (not sure about 2007) and if you can filter you can count using subtotal.

Happy to be here
Oklahoma City, OK
reply to 2kmaro
2kmaro See Profile is right. Both websites are great ideas on how to do this.

I would caution you on this though. I made a UDF that counts by color and it fails on some of the colors going between Excel 2010 and 2013 especially on the Reds that change to Orange in 2013, so it's not perfect.

Also all it would take even if you didn't change Excel versions is for one or more cells to be formatted with a ever so slightly different color that you don't or can't distinguish and then you have incorrect totals.

He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot


Oklahoma City, OK
reply to jaa
I use Chip Pearson's functions extensively and have found them extremely useful when Excel doesn't provide a reasonable answer for the need.

Make sure you understand what the function is requiring as arguments. Some of them can get really picky and the documentation is a bit obscure sometimes.