Counting cells by colour

[scooby51]

Hi All,

I am wondering if there is formula whereby I can count cells based on their colour?

 

[Scott Huish]

This question has been asked a lot.

There are some workaround sort of things, but generally:
If the cell is colored by Conditional Formatting, you can write a formula to count by the same condition that Conditional Formatting is using.
Otherwise, it will take VBA.

https://www.got-it.ai/solutions/excel-chat/excel-tutorial/count/how-to-count-colored-cells

 

[alansidman]

This will require a VBA solution and you will need to know the colorindex number for the background. See this similar request.

https://www.excelforum.com/excel-fo...unt-number-of-cells-of-particular-colour.html

 

[RasGhul]

Hi,

Right Click on your Sheet tab, View Code. This will open the VBA window for this sheet code.

Paste;

Sub Listcolors()

For Each C In Selection


    C.Offset(0, 1).Value = C.Interior.Color


Next C




End Sub

Highlight the the range of cells with the color, then run the macro. Avoid highlighting the whole column only the range you need.

The macro will populate the color number in the cells to the right, which you can use with countif & sumifs etc.