Count by Color

[smalik]

Is there a way to count cells by a specific color?

 

[smalik]

For example, I have a range of cells A1 through H1. However, only three cells are highlighted in Yellow. How do I count this by using a formula?

Do I need to know the code for the color? i,e., light yellow vs. dark yellow where do I find the code? Is that the HEX code?

 

[Fluff]

It can only be done using VBA. Formulae cannot see the colour of a cell.

 

[dreid1011]

You cannot count cell color by formula... unless you are coloring the cells with a conditional formatting formula, then you could use the same formula conditions to count cells. Otherwise, you have to use VBA.

 

[hagia_sofia]

You could use a custom LAMBDA function, i.e. to define a name (you can name it e.g. COLOR):

=LAMBDA(array,MAP(array,LAMBDA(a,GET.CELL(63,a))))

This will allow you to call your custom function, specifically to select a range of cells and return a list of corresponding numbers for different colors; then you could use this helper column as an input for another Excel function such as COUNTIFS or SUMIFS.

 

[smalik]

You cannot count cell color by formula... unless you are coloring the cells with a conditional formatting formula, then you could use the same formula conditions to count cells. Otherwise, you have to use VBA.

I am coloring them using the conditional formatting. However, each column has a different condition. Some columns are highlighted because they are blank, and some are highlighted because they do not meeting the specific criteria for that field.

 

[smalik]

You could use a custom LAMBDA function, i.e. to define a name (you can name it e.g. COLOR):

=LAMBDA(array,MAP(array,LAMBDA(a,GET.CELL(63,a))))

This will allow you to call your custom function, specifically to select a range of cells and return a list of corresponding numbers for different colors; then you could use this helper column as an input for another Excel function such as COUNTIFS or SUMIFS.

I am not familiar with VBA, Is this a VBA Code? how do I use it? Can I get an example using countifs? Thanks.

 

[hagia_sofia]

Cf. the picture: 1) copy the formula to "Refers to:", 2) type COLOR in "Name:", 3) Hit OK; 4) type =COLOR in a selected cell and select the range of cells where you would to distinguish colors, 5) the formula should return an array of numbers corresponding to cell colors. 6) E.g. yellow color will be represented as 6 which you can in another standard formula (e.g. COUNTIFS all cells where there is 6 will represents all yellow cells). The only thing to remember is that it will not recalculate automatically when you change background color of a cell (you can address this by e.g. entering the cell and hitting Enter without changing anything).