Colored cells counting

  • Thread starter Thread starter OtaMerOta
  • Start date Start date
O

OtaMerOta

Hi everybody,
I have a small problem.
I would like to have a cell which counts the cells with different background or with different text colour.
E.g. the cell B5 should be the sum of colored red cells A1, A3, A4.
And the same thing with the text color, this means to count the cells with blue color.

Thanks in advance,
Kastriot.
 
I can't think of a way to do it directly, but if the text & cell colouring
follows some rules of logic based on the cell contents, then you could do it
indirectly by testing the cell contents and returning a "colour code" in an
unused area, and then counting up the various results.
eg if cell contents = 0 then text = red and colour code = 1, if cell
contents = text then text = blue and colour code = 2
Then use a formula like =CountIf to find all the 1's, 2's, etc.
Rgds,
ScottO

Hi everybody,
I have a small problem.
I would like to have a cell which counts the cells with different background
or with different text colour.
E.g. the cell B5 should be the sum of colored red cells A1, A3, A4.
And the same thing with the text color, this means to count the cells with
blue color.

Thanks in advance,
Kastriot.
 
As long as the colour is not set by conditional formatting, see http://xldynamic.com/source/xld.ColourCounter.html but note the constraints.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Hi everybody,
I have a small problem.
I would like to have a cell which counts the cells with different background or with different text colour.
E.g. the cell B5 should be the sum of colored red cells A1, A3, A4.
And the same thing with the text color, this means to count the cells with blue color.

Thanks in advance,
Kastriot.
 
Hi Kastriot,
You should not rely on matching a color because colors are hard
to distinguish you should be using some other kind of tag directly
viewed and countable on the worksheet. Changing the color of a
cell does not create an Event, so if you want immediate results,
you can't, but you can add volatile (bad practice to rely on) to
a user defined functions.

If you are the only user, it might be better to know that a function
will not be updated by leaving off Volatile and by forcing a
complete recalculateion Ctrl+Alt+F9 when you need updated
content, or by updating when the sheet is activated using an
Event macro.

Anyway see Chip Pearson's page
Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

colors: http://www.mvps.org/dmcritchie/excel/colors.htm#cpcolorsx
volatile: http://www.mvps.org/dmcritchie/excel/shortx2k.htm#volatile
 
Back
Top