SumByCellColor function

SUMBYCELLCOLOR() Description

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

Unfortunately, LO 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 get really cumbersome. This often results in complex formulas that are prone to errors when making changes.

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

Main Features of the SUMBYCELLCOLOR Function

Syntax:

=SUMBYCELLCOLOR(RangeToSum; SampleCell)

Parameters:

• RangeToSum: The range of cells to be summed.
• 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 LO Calc automatically sums cells with the same color:

=SUMBYCELLCOLOR(RangeToSum; SampleCell)

We will have the following result:

This example uses the following values:

• RangeToSum: B4:H4 - A range with cells that contain working hours.
• SampleCell: B10 - The 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 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 don't have text or data in them.
• Can I use color as a condition in SUMIF? I have a column that I need to sum, but only the cells are yellow.
• I noticed that in recent versions LO Calc you can sort by cell color and font. What about the function LO Calc ? Can formulas also work with cell and font colors?
• How do I count or sum cells by their fill or background color? It seems that in LO Calc There is no formula that allows you to count or sum a range of cells based on a specified background color.
• Several of my colleagues have been tracking their work by manually coloring cells based on who was working on a particular task (e.g. John colors the cells he worked on blue, Susan colors them red, Daniel colors his yellow). I need to get the total of each color. I'm looking for something automated so we don't have to sort by color every time to get a total. The problem seems to be that they manually color the cells, so there's no criteria to base the total on other than the colors.

Notes on the calculation

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

Download extension

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

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