Counting Coloured Cells

[mpantalo]

Hi,

I have a spreadsheet that has cells coloured either grey or white. Is there a formula I can use to count the number of white (or grey) cells?

Regards
mpantalo

 

[Stormseed]

Check this:

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

 

[Fazza]

Hi,

I'm assuming you want a non-VBA solution. If you want a VBA solution then please search the archives or some websites - maybe Chip Pearson's or John Walkenbach's or Debra Dagliesh's?

A special formula can be used in a helper column to return the code number for the colour and then you can use a standard COUNTIF on this helper column. Here is an old post that discusses the method http://www.mrexcel.com/board2/viewtopic.php?t=213224

Some explanation, this functionality is not routine but is available via an old Excel4 macro function that can be used in a formula in a defined name. It can not be used directly in a worksheet formula. Create the defined name formula remembering to set up the formula for relative referencing (the active cell when you create the formula relative to the cell that you want to test). It doesn't work on colours created from conditionally formatting and doesn't update automatically with changes to cell colours. If this approach is not OK for you, you'll need some VBA.

Cheers, Fazza

 

[Scott Huish]

Are the cells colored by conditional formatting or not?

If by conditional formatting, what is the condition to format the cells? And in what range?

 

[mpantalo]

Hi,

The cells are not conditionally formatted. They are simply coloured differently.

 

[Stormseed]

Hi,

The cells are not conditionally formatted. They are simply coloured differently.

the answer to your question is in the link above which I have posted.