NB.IF color criteria
Solved
albelo03
Posted messages
9
Status
Membre
-
Wanam -
Wanam -
Hello,
I want to count the number of cells based on the background color of the cells. I think I will use the COUNTIF function, but I don't know what to put as criteria.
Thank you in advance for your help.
I want to count the number of cells based on the background color of the cells. I think I will use the COUNTIF function, but I don't know what to put as criteria.
Thank you in advance for your help.
10 réponses
Re,
I have to step out, but if you're following, we can do it by formula but it requires a bit of trickery
On your table, activate cell L3 and go to Insert/Name/Define
in the name in the workbook, enter for example color
in Refers to, paste this formula
=GET.CELL(38,numbering!D3)+(RAND()>1)
and OK
while your cell L3 is still activated, in the formula bar enter
=color
and drag down to the bottom of your table
to count the yellow cells in a cell, enter this formula
=COUNTIF(L3:L585,36)
for the beige-orange
=COUNTIF(L3:L585,40)
and for the gray
=COUNTIF(L3:L585,15)
A+
Mike-31
A period of failure is the perfect time to sow the seeds of knowledge.
I have to step out, but if you're following, we can do it by formula but it requires a bit of trickery
On your table, activate cell L3 and go to Insert/Name/Define
in the name in the workbook, enter for example color
in Refers to, paste this formula
=GET.CELL(38,numbering!D3)+(RAND()>1)
and OK
while your cell L3 is still activated, in the formula bar enter
=color
and drag down to the bottom of your table
to count the yellow cells in a cell, enter this formula
=COUNTIF(L3:L585,36)
for the beige-orange
=COUNTIF(L3:L585,40)
and for the gray
=COUNTIF(L3:L585,15)
A+
Mike-31
A period of failure is the perfect time to sow the seeds of knowledge.
Thank you very much
If you have any other color codes, feel free to include them.
As for me, in Excel 2010, I used the following formula =SumIfColorBackground(A1:A5;6)
6 for the yellow color and because I wanted the sum.