How do I count cells with a specific cell shading?

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mistermond]

That works a treat! Thanks.
However, I have an additional complication in that I only want to count
cells of a specific colour AND with non-zero values. I'm afraid my VBA isn't
up to it!
Any idea?
Ray Mount

 

[Bob Phillips]

It is counted by VBA, the VBA returns an array of matching colours, it is
the SP formula. So use something like

=SUMPRODUCT(--(C1:C1000<>0),--(ColorIndex(C1:C1000)=Colorindex(A1))

where A1 is coloured with your test colour.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mistermond]

Spot one once again!
Thanks very much - it has saved me a lot of effort now and in the future.
Ray Mount