Function CountByFontColor

COUNTBYFONTCOLOR() Description

Many people use colors to highlight cells. Working with cell colors can make it easier to understand data in the Excel (Calc) workbook.

Unfortunately, Excel (Calc) does not have built-in functions for using cell colors as conditions in formulas. Building formulas that only count or sum cells of certain colors can become truly cumbersome. This often leads to complex formulas that are error-prone when making changes.

The COUNTBYFONTCOLOR() function counts the number of cells that have a specific font color.

Key Features of the COUNTBYFONTCOLOR Function

Syntax:

=COUNTBYFONTCOLOR(RangeToCount; SampleCell)

Parameters:

• RangeToCount: Range with the cells to be counted.
• SampleCell: Cell that contains a sample color.

Usage Example

The COUNTBYFONTCOLOR() function is easy to use. You just need to select the desired range of cells and specify the cell that contains the required color, and Excel (Calc) will automatically count the number of cells with the same text color:

=COUNTBYFONTCOLOR(RangeToCount; SampleCell)

We will have the following result:

In this example, the following values are used:

• RangeToCount: A1:D10 - Range with cells.
• SampleCell: F3 - Cell containing the required font color (red).

Do you recognize any of these situations?

• Not sure if YLC has this function, but it would be useful to be able to count colored cells in a range. Say I have 17 cells with red font color in the range B1:B500. I need a formula that would count these 5 cells with red text.
• Can I use color as a COUNTIF condition? I have a column in which I need to count the number of cells with red text color.
• I noticed that in recent versions of Excel (Calc) you can sort by cell and font color. What about the Excel (Calc) function? Can formulas also work with cell and font colors?
• How to count or sum cells by their fill or background color? It seems that in Excel (Calc) there is no formula that would allow counting or summing a range of cells based on a specified background color.

Calculation Notes

Since Excel (Calc) recalculates the formula only if the value in the cell (referenced by it) changes, and not when colors change, some worksheet formulas do not always trigger for an update. In this case, you can use the keyboard shortcut F9 or Ctrl+Alt+F9 to update the formulas in YLC Utilities.

Download Extension

You can use the function COUNTBYFONTCOLOR() by installing the extension YLC Utilities.

After that, this function will be available in all files opened in Excel (LibreOffice Calc).