SumByCellColor function for LibreOffice Calc

SUMBYCELLCOLOR() Description

Many people use colors to indicate cells. Working with cell colors can make it easier to understand the data in the LO Calc workbook.

Unfortunately, LO Calc has no built-in functions for using cell colors as conditions in formulas. Building formulas that only count or sum cells of certain colors can get really cumbersome. This often results in complex formulas that are prone to error when making changes.

The SUMBYCELLCOLOR() function sums the values in cells that have a specific fill color.

Main Features Functions SUMBYCELLCOLOR

Syntax:

=SUMBYCELLCOLOR(Range; SampleCell)

Parameters:

• Range: The range of cells to sum.
• SampleCell: A cell that contains a color sample.

Example Usage

The SUMBYCELLCOLOR() function is easy to use. You just need to select the desired range of cells and specify the cell that contains the desired color, and LibreOffice Calc will automatically sum the cells with the same color:

=SUMBYCELLCOLOR(B4:H4; B10)

We will have the following result:

This example uses the following values:

• Range: B4:H4 - A range with cells that contain working hours.
• SampleCell: B10 - A cell that contains the required color (green).

Do you recognize any of these situations?

• Not sure if YLC has this feature, but it would be useful to be able to count the colored cells in a range. Let's say I have 17 pink cells in the range B1:B500. I need a formula that counts those 5 pink cells even if they have no text or data.
• Is it possible to use color as a SUMIF condition? I have a column that needs to be summed, but only the cells are yellow.
• I noticed that in recent versions of LO Calc can sort by cell color and font. What about the LO Calc function? Can formulas also work with cell and font colors?
• How to count or sum cells by their fill or background color? There doesn't seem to be a formula in LO Calc that would count or sum a range of cells based on a specified background color.
• Several of my colleagues kept track of their work by manually coloring the cells based on who was doing a particular task (for example, John would color the cells he was working on blue, Susan would color red, Daniel would color his yellow). I need to get the total number of each color. I'm looking for something automated so we don't have to sort by color every time to get a summary. The problem seems to be that they manually color the cells, so there is no criteria to base the sum on other than the colors.

Notes on the calculation

Because LO Calc only recalculates the formula when the value in the (referenced) cell changes, not when the colors change, some formulas on the worksheet don't always trigger to update. In this case, you can use F9 or Ctrl+Alt+F9 to update the formulas in YLC Utilities.

Download the extension

You can use the SUMBYCELLCOLOR() function by installing a free extension YouLibreCalc.oxt or its full-featured version YLC Utilities.oxt .

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