You can use XL4 macros (Excel formula) to count up cells with different backcolor or even font colour in excel 🙂 See this LINK. For Font color the type_num
is 24
. And for backcolor we will use 63
- Open the Name Manager
- Give a name. Say
BackColor
- Type this formula in Refers To
=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-1,0))
and click OK
The explanation of =GET.CELL()
is mentioned in the above link.
Now let’s say your workbook looks like this
Next put this formula in row 2.
=backcolor
Next put =COUNTIF(A2:J2,8)
and =COUNTIF(A2:J2,7)
in cell C5
and C6
respectively and you will get the total count of colors.