Counting by color

When creating tables, we often use our own markup, e.g. change the cell background color of the data we want to indicate in some way. How to automatically count them later? If we applied a filter or these cells are within a structured table, it is enough to filter the table by color, so that we can use SUBTOTAL function to find out how many cells are colored in a certain way. Can this also be done using COUNTIF?

In the example we will use a spreadsheet containing the names of employees, organizational units and salaries. Few employees have too small salaries which should increase and the CEO marked them in yellow. How to count these cells? First, press CTRL + F3 to enter Name Manager and create a new name (color). Instead of a cell reference we will enter the formula:

 = GET.CELL (38, C2)

GET.CELL is an old macro function that reads cell attributes. In our example, it is the background color of cell C2, and by copying this value will be updated so we can use it for other cells as well. Once we have created a new name we can use it to create a column to the right of the one with salaries. The values ​​we get by copying a formula represent the background color. In the end, you just need to create a formula in the given cell that counts all those cells that have a value of 6 (yellow):

= COUNTIF (D2: D11, 6)

When we initially created the name with the formula, we looked for the cell value to the left of the formula. We can use this now, so if we assign a yellow background color to the cell on the left, the formula might look something like this:

= COUNTIF (D2: D11, color)

The result is the same, four workers should receive a raise!