Counting filled cells

[A]

I need to count the number of coloured cells within a range. Is there any
formula to do this ?


Thanks in advance

S

 

[A]

Sorry, I should of read a few posts up (post from spden1). Looks way too
complicated for me.

S

 

[Frank Kabel]

Hi
this isn't that complicated
Just paste the code provided in this post in your VBA editor (you may
have a look at http://www.mvps.org/dmcritchie/excel/getstarted.htm for
how to do this)

After this just call it as a normal function. If you have problems with
that you may email me a workbook and I'll include this macro for you

 

[Bob Phillips]

A,

Nothing in life comes easy<vbg>

Really, it only looks complicated because it is a full solution, not just an
idea or an update of existing code. If you copy the code into a standard
code module, you have a working utility that you can just use and forget
about. You don't need to understand how it works, why it works etc., just
use it. Using it is very simple, once the code is in your workbook, you get
the colour of a cell with

=ColorIndex(A1)

or the colour of the text with

=ColorIndex(A1,True)


To sum a range of cells, just use

=SUMPRODUCT(--(Colorindex(A1:A100)=3)) ' 3 is red

or better still put red in a cell say B2 and use

=SUMPRODUCT(--(Colorindex(A1:A100)=ColorIndedx(B2)))

I wrote the routine specifically to make this oft-requested function easy to
use.