Function SumByFontColor

SUMBYFONTCOLOR() Description

Many people use colors to mark cells. Working with cell colors can facilitate understanding data in the workbook Excel (Calc).

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 specific colors can become truly cumbersome. This often leads to complex formulas that are prone to errors when making changes.

The function SUMBYFONTCOLOR() sums values in cells that have a specific font color.

Main Features of the SUMBYFONTCOLOR Function

Syntax:

=SUMBYFONTCOLOR(RangeToSum; SampleCell)

Parameters:

• RangeToSum: The range of cells to sum.
• SampleCell: The cell that contains the sample color.

Usage Example

The SUMBYFONTCOLOR() 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 sum cells with the same color:

=SUMBYFONTCOLOR(RangeToSum; SampleCell)

We will have the following result:

This example uses the following values:

• RangeToSum: A1:D10 - Range with cells.
• SampleCell: F3 - The cell that contains 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. Let's 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 color be used as a SUMIF condition? I have a column that needs to be summed, but I only need 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 function Excel (Calc)? Can formulas also work with cell and font colors?
• How to count or sum cells by their fill or background color? It seems that Excel (Calc) does not have a formula that would allow counting or summing a range of cells based on a specified background color.

Calculation Notes

Since Excel (Calc) recalculates a formula only if the value in a cell (to which it refers) changes, and not when colors change, some worksheet formulas do not always run to update. In this case, you can use the F9 or Ctrl+Alt+F9 key combination to update formulas in YLC Utilities.

Download Extension

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

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