CountByCellColor function

COUNTBYCELLCOLOR() Description

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

Unfortunately, Excel (Calc) has no built-in functionality 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 COUNTBYCELLCOLOR() function counts the number of cells that have a specific fill color.

Main Features of the COUNTBYCELLCOLOR Function

Syntax:

=COUNTBYCELLCOLOR(RangeToCount; SampleCell)

Parameters:

• RangeToCount: The range with the cells to be counted.
• SampleCell: A cell that contains a color sample.

Example Usage

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

=COUNTBYCELLCOLOR(RangeToCount; SampleCell)

We will have the following result:

This example uses the following values:

• RangeToCount: B4:H4 - A range with cells that contain marks about days worked.
• 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 for COUNTIF? I have a column where I need to count the number of cells that are yellow.
• I noticed that in recent versions Excel (Calc) you can sort by cell color and font. What about the function Excel (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 Excel (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.

Calculation notes

Because Excel (Calc) recalculates the formula only when the value in the cell (which it references) changes, not when the colors change, some worksheet formulas 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 the extension

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

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