 2kmaroThinkPremium,ExMod 1 BC
join:2000-07-11
ColossalCave | reply to jaa
Re: [Excel] Count cells with a red background? 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.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. |
 H2OuUp2Happy to be herePremium
join:2002-03-15
Oklahoma City, OK | 2kmaro  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 |
