Do you need to count your data based on font color?

Google Sheets users often use font colors to highlight the data that is important and needs attention. This eventually allows them to manage the key figures more precisely.

However, when they want to count the data based on the cells with different font colors, it is pretty tricky, and not everyone knows about it.

Throughout this article, you will learn the different methods by which you can count the cells by font color inside Google Sheets.

You will also create a cool custom function of your own that works in all scenarios and allows you to count the cells based on the font color.

Count by Font Color with the SUBTOTAL Function

The dataset used in these examples holds the four columns ID (employee id), First (First Name), Last (Last Name), and Salary (Salary in USD) in columns A to D, respectively.

The database consists of 10 employees whose details are spanned across ten rows. A few of the employees are color-coded with a red font.

To count the cells by font colors, you can use the SUBTOTAL function in combination with the Filter Toggles from Google Sheets.

Follow these steps to apply the Filter Toggle on this data.

1. Select the entire data range (A1 to D11 in this case) on which the filter needs to be applied.
2. From the ribbon menu, click on the Data menu to access the available options.
3. Select the Create a filter option to apply a filter on the selected range of cells.

1. Once the filter is applied, you can see a Filter Toggle at the right-hand side of each column header. It looks like a reverse pyramid-like structure. Click on it to access all filtering options.
2. Click on the Filter by color dropdown from the filtering options.
3. Select the Text color option.
4. Select the red color option as you wish to count the cells with the font color red.

📝 Note: The Filter Toggle is applied for each column, and it is fine if you select any of the four columns to filter the red-colored values from the data.

You will see a filtered view where all the cells with the font color red are shown in the view.

Now that you have filtered the rows based on the font color, you are ready to use the SUBTOTAL function to count the rows with red font color.

= SUBTOTAL ( 103, B2:B11 )

In cell B13, type the above function to count the cells by font color.

Explanation

The SUBTOTAL function is known for allowing users to calculate many types of aggregation.

The first argument of the function above specifies the aggregation. 103 means the SUBTOTAL function will apply the COUNTA aggregation on the visible data. COUNTA function counts the number of values in a given range.

The second argument is for the data range to count the values based on font colors. Hence the range is selected as B2:B11.

📝 Note: You can select any column range for this operation as the font color when applied, is the same for each row of all columns. This means instead of B2:B11, you are free to use D2:D11, A2:A11, or C2:C11, and the result will be the same.

The thing about using this method is as soon as you remove the filter of colored cells, the SUBTOTAL function will consider all the cells in range and give the count of all cells rather than only the ones with the font color red.

Follow the steps below and see for yourself.

1. Go to the Filter Toggle from any of the column headers and then click on it.
2. Inside the filter menu, go to the Filter by color dropdown
3. Click on None to remove all the filtering rules.

Now, you can see that the SUBTOTAL function gives a count of 10 instead of 4. Because the rows are now unfiltered, the function considers every value between the cell range B2:B11.

Count by Font Color with an App Script Custom Function

As you already know, no built-in function inside Google Sheets can allow you to count the cells by font color.

However, using the Apps Script to create a custom function of your own is a common practice that everyone follows to automate the tasks they have in hand.

This method will use the same to create a custom function that will capture the count of cell values based on the font color.

If you want to know more about the Apps Script, check out this comprehensive beginner’s guide on App Scripts.

To launch the Apps Script, go to the Extensions menu from the ribbon and click on the Apps Script option. A new browser tab will open up and load with the Apps Script.

/**
* This function will return the count of cells in the given range of cells based on a given color
* @param1 range - This is a cell reference with the color to count.
* @param2 cell - This is the range of cells to count.
* @returns the count of coloured cells 
* @customfunction
**/
function COUNTFONTCOLOR(range,cell) {
  var currRange = SpreadsheetApp.getActiveRange();
  var currSheet = currRange.getSheet();
  var currCellFormula = currRange.getFormula();

  //Parse the first parameter and convert to a range reference
  var para1 = currCellFormula.match(/\((.*)\,/).pop();
  var countRange = currSheet.getRange(para1);
  var countRangeFC = countRange.getFontColors();
  
  //Parse the second parameter and convert to a range reference
  var para2 = currCellFormula.match(/\,(.*)\)/).pop();
  var goalCell = currSheet.getRange(para2);
  var goalCellFC = goalCell.getFontColor();
 
  var counter = 0;

  for(var i=0;i<countRangeFC.length;i++) {
    for(var j=0;j<countRangeFC[0].length;j++) {
      if( countRangeFC[i][j] === goalCellFC){
        counter++;
      }
    }
  }
  return counter;
};

Copy and paste the above code into the Apps Script editor and click on the Save button.

Warning: Don’t click on the Run button. The general tendency is to click on the Run button to run the Apps Script code after saving it. In this case, we are just creating a custom function that can be used within the sheets as standard functions do.

⚠️ Warning: Don’t click on the Run button. The general tendency is to click on the Run button to run the Apps Script code after saving it. This example creates a custom function that can be used within the sheets similar to standard functions.

Explanation

• The first line of code will create a function named COUNTFONTCOLOR() with two parameters range and cell.
• You then define the range and cell in the following two lines. The methods used are SpreadsheetApp.getActiveRange() and getSheet(). The results are stored in two variables, currRange, and currSheet, respectively.
• The following line allows you to grab the formula from the cells using the method currRange.getFormula(), and results are stored inside the currCellFormula variable.
• The para1 variables define matching criteria that match with the string in a particular range (in your example, it will be B2:B11) with a specific regular expression entered and then return an array of cells that match the criteria. It is achieved with the currCellFormula.match(/\((.*)\,/).pop() method.
• In the next line, the array of cells from para1 is now being counted from the current sheet range.
• The getFontColors() method is used on the next line to capture the font colors from the entire array that was returned by para1 and counted. This means the three lines of code in this section capture the font color from a specified range.
• The same method is used to create another variable para2, which defines matching criteria for another regular expression and returns an array of cells that follow this criterion.
• The same steps are again used to extract the count and font color of those cells that follow the second regular expression criteria. The difference is, in this section, the line of codes are only capturing the font color from a specified cell where the comparison will happen.
• Finally, you run a for(){} loop that allows you to run through all the range values that match with the particular font color from the cell you mentioned and return the count of all such cells.

Now, your function with the name COUNTFONTCOLOR() is ready to be used under Google Sheets. In cell B13, use the following formula to count the cells in which the font color is set to red.

=COUNTFONTCOLOR(B2:B11,A13)

The function contains two parameters.

• range – which specifies the range from which you want to match the font color
• cell – that specifies the cell with which the font color from ranges should match.

📝 Note: Pay attention to cell A13 which is used as the function parameter. The text in it is colored red. This is used to compare and count the font colors in the custom code. For example, If you keep the font color from cell A13 as black, the value in B13 would be 6 instead of 4.

Conclusions

Counting the cells with designated font colors is needed for an hour when you are summarizing the data after analysis. However, no direct way of getting it done is available inside Google Sheets.

The SUBTOTAL function, when used in combination with the Filter Toggle and COUNTA function, can allow you to capture the count of cell values with a specified font color, given that the data is filtered for only those rows. After you remove the filter, the SUBTOTAL function will give the count of all visible rows in the range.

Another way is to create a custom function named COUNTFONTCOLOR that captures the font color of cells from your range and counts the results.

Did you find this useful for counting your different font colors? Let me know in the comments!