Count all cells by color - blanks and non-blanks

[Thomas Kuriakose]

Dear Sirs,

I tried subtotal function with count A to get the count of colored cells, but this is giving correct result only when the cells have a value, if the cell value I blank, it is not counting the color.

Kindly check this sheet, we need count of all cells with a specific color.

Thank you so much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

There is no in-built function to count colored cells , unless Excel 2013 has something.

What the SUBTOTAL trick does is basically count the visible cells , or rather ignore the hidden cells. Since the COUNT or even the COUNTA function will count only non-blank cells ( with numeric data in the case of COUNT and alphanumeric in the case of COUNTA ) , unless you have data in the colored cells , nothing can be done.

The filter is to be applied using the Filter By Color option so that only cells of a particular color are visible , after which the SUBTOTAL function will return the correct result.

You can use a helper column which has 1s in it ; the Filter By Color will hide the unwanted rows , and the SUBTOTAL function can use the helper column to return the result.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you so much for this solution.

with regards,
thomas