How to count and sum cells by color in Google Sheets: Use Cases

To count or sum cells by fill or font color in Google Sheets, open the Function by Color add-on, select your cell range, choose your target color pattern, and select an operation like SUM or COUNTA. The tool automatically generates dynamic formulas to calculate your totals.

For standard Google Sheets formulas, color is not data. They only read cell values 🤷‍♀️. To perform calculations based on cell color, you’d have to create an extra helper column and type text flags. Only then could you use standard COUNTIF (opens in a new tab) or SUMIF (opens in a new tab) formulas on those helper column values.

With the Function by Color (opens in a new tab) extension (also included in Power Tools (opens in a new tab)), you can calculate totals, averages, and counts directly from cell background fill and font text colors.

Case 1: Count cells by color to track project progress in Google Sheets

Scenario:

A project manager tracks sprint deliverables across a team of developers and designers in Google Sheets. Every cell contains the unique name of a specific task, while progress statuses are represented using background fill colors:

• 🟩 Green: Completed tasks.
• 🟨 Yellow: Active tasks.
• 🟥 Red: Delayed or blocked tasks.

The goal is to count total completed tasks across the entire project and also completed tasks per employee to track individual output.

Problem:

• Formula limitations: Standard functions like =COUNTIF() cannot read colors. To use them, you’ll need a helper column with text statuses like “Done” or “In Progress” next to every single task.
• Complex counting: Counting how many completed tasks a specific employee holds requires constructing even more complex formulas.
• Apps Script difficulty: Writing custom Apps Script requires technical expertise, and risks breaking when non-technical team members edit it.

Solution:

The Function by Color extension (available as part of Power Tools or Function by Color) allows you to count cells based on specific color.

How it works:

Step 1: Launch Function by Color

1. Highlight your team’s task grid.
2. Go to Extensions › Power Tools › Start.

3. In the Power Tools menu, select Process › Function by color.

Step 2: Count overall completed tasks across the project

1. Go to the One color tab in the sidebar.
2. Click the Color picker eyedropper icon and click any green task cell on your sheet to set the pattern.
3. From the Use function drop-down list, select COUNTA (to count all cells with text).
4. In the Paste results to field, specify the cell where you want the result to appear.

5. From the Calculate in drop-down, choose entire range to count the overall total across the full task grid.
6. Click Insert function.

The add-on places a dynamic formula returning the total number of finished tasks across the entire table.

Step 3: Count completed tasks per employee

1. Keep the One color tab open with your green pattern color selected.
2. In the Paste results to field, select the target cells or column range next to your employees’ rows.

3. From the Calculate in drop-down, choose each row.
4. Click Insert function.

The add-on instantly calculates the number of completed (green) tasks for every employee.

Key benefits:

• No extra columns: Track statuses visually using colors without forcing team members to enter duplicate text values.
• Dynamic formulas: The add-on places formulas directly inside your sheet that can be recalculated at any time.
• Zero coding: Eliminates the need to write Google Apps Script triggers.

Case 2: Calculate work hours: sum and count cells by fill and font color in Google Sheets

Scenario:

An HR manager tracks employee attendance and shift hours in Google Sheets. Every cell in the timesheet contains the number of worked hours, while formatting rules indicate the specific shift type and attendance status:

Background fill color (Shift type):

• ⚪ White fill: Day shift (Standard rate)
• 🟦 Blue fill: Night shift (Premium pay rate)

Font color (Attendance status):

• 🟩 Green text: Standard shift.
• 🟧 Orange text: Overtime.
• 🟪 Purple text: Short shift.
• 🟥 Red text: Sick leave / Absence.

To process payroll correctly at the end of the month, the manager needs to calculate:

• Total night shift hours per employee summing numbers in blue cells.
• Total sick leave days per employee counting red text entries.

Problem:

Standard formulas cannot distinguish between day hours and night hours, as they only see numeric values, but not their color.

Solution:

With Function by Color, you can sum values or count cells based on both fill color and font color. You can easily switch calculation functions (like SUM or COUNTA) to handle timesheet management.

How it works:

Step 1: Launch Function by Color

1. Select your timesheet data grid (excluding employee names).
2. Go to Extensions › Power Tools › Process › Function by color.

Step 2: Calculate total night shift hours per employee (Sum by fill color)

1. Go to the One color tab in the sidebar.
2. Check the box for Fill color and select the blue color used for night shifts.

3. From the Use function drop-down, choose SUM to add up the worked hours inside those blue cells.
4. In the Paste results to field, select the target column range for night hours next to your employee rows.
5. From the Calculate in drop-down list, choose each row.
6. Click Insert function.

The add-on places dynamic formulas into your sheet, adding up only the hours worked during night shifts for each employee.

Step 3: Count sick leave days per employee (Count by font color)

1. Keep the One color tab open.
2. Check the box for Font color and select the red color used for sick leaves.

3. From the Use function drop-down list, select COUNT.
4. In the Paste results to field, specify the target column range for sick days.
5. From the Calculate in drop-down, choose each row.
6. Click Insert function.

The add-on calculates the exact number of sick days taken by each team member by tracking the red font color.

Key benefits:

• Flexible color tracking: Works with both background fill colors and font text colors.
• Supports various functions: Sum numeric values, count filled cells, or calculate averages based on cell or font color.

Case 3: Track expenses: sum and average by cell color in Google Sheets

Scenario:

A finance manager tracks departmental marketing budgets and actual expenditures in Google Sheets. Every cell contains a dollar amount, while fill colors indicate budget allocation and expense flags:

• 🟩 Green fill: Approved budget lines.
• 🟨 Yellow fill: Cautionary items.
• 🟥 Red fill: Over-budget expenses.

The goal is to:

• Sum total over-budget expenses (red cells) for each quarter to track departmental cost overruns over time.
• Calculate the average approved expenditure (green cells) to set benchmarks for the next quarter.
• Generate a full summary table of all budget categories (green, yellow, red) to have a budget overview.

Problem:

Standard Google Sheets functions like =SUMIF() or =AVERAGEIF() (opens in a new tab) require specific numeric values written inside helper columns.

When expenses are visually flagged using background colors during budget reviews, standard formulas cannot calculate totals or averages based on those colors. Writing custom scripts or rebuilding financial sheets with complex tags adds unnecessary overhead.

Solution:

With Function by Color, you can sum values, calculate averages, or build a complete summary table directly from cell colors.

How it works:

Step 1: Launch Function by Color

1. Select your financial expense range.
2. Go to Extensions › Power Tools › Process › Function by color.

Step 2: Sum over-budget expenses per quarter (SUM by fill color)

1. Go to the One color tab in the sidebar.
2. Check the box for Fill color and select the red color used for over-budget items.
3. From the Use function drop-down list, select SUM to add up amounts in red cells.
4. In the Paste results to field, select a horizontal row under your table corresponding to your quarterly columns.
5. From the Calculate in drop-down, choose each column.
6. Click Insert function.

The add-on inserts dynamic formulas across columns giving you the total over-budget spend for each quarter individually.

Step 3: Calculate average approved spend (AVERAGE by fill color)

1. Keep the One color tab open.
2. Select the green color pattern used for approved budgets.
3. From the Use function drop-down, choose AVERAGE.
4. In the Paste results to field, specify an empty cell for your benchmark metric.
5. From the Calculate in drop-down, choose entire range.
6. Click Insert function.

The tool inserts a formula calculating the mean expense amount for all approved budget lines across the entire range.

Step 4: Generate a complete budget breakdown by color

1. Switch to the All colors tab at the top of the sidebar.
2. In the Select range field, highlight your full budget table.
3. From the Calculate by drop-down list, select fill colors.
4. From the Use function drop-down, select SUM.
5. In the Paste results to field, choose an empty area on your sheet.
6. From the Calculate in drop-down list, select entire range.
7. Click Insert function.

The add-on instantly builds a clean matrix listing every color pattern alongside its total dollar sum.

Key benefits:

• Advanced function support: Easily switch between functions like SUM, AVERAGE, MIN, and MAX based on your reporting needs.
• Instant breakdowns: Use the All colors tab to build comprehensive summaries in seconds without writing multiple formulas.

Supported calculation functions in Function by Color

Frequently Asked Questions

How to sum cells based on fill color in Google Sheets?

To sum cells by background fill color:

1. Open Function by Color from the Extensions menu.
2. Select the range containing your values.
3. In the One color tab, check Fill color and pick your target shade using the eyedropper icon.
4. Set the Use function drop-down to SUM, then choose where to paste the result, and click Insert function.

Can you do a COUNTIF based on text color in Google Sheets?

Native =COUNTIF() cannot evaluate text color, but Function by Color can! In the One color tab, check Font color, pick your target text shade, and set the function to COUNTA (or COUNT). The add-on will count only the cells that match that specific text color.

How is COUNTA different from COUNT when counting by color?

Use COUNTA to count all non-empty cells (text, numbers, or symbols) of a specific color. Use COUNT if you only want to count numeric entries within those colored cells.

How do I count the number of colored cells in a row?

Select your data range and launch the Function by Color extension. Choose your target fill or font color, set the function to COUNTA, select your output column, and select each row from the Calculate in drop-down. Click Insert function to generate row-by-row counts.

Can I count or sum cells formatted with Conditional Formatting?

Yes! The add-on recognizes both standard manual fill/font colors and custom colors generated by standard Conditional Formatting rules.

What happens if a cell has both a fill color and text color selected?

You have full control. In the One color tab, you can choose to check only Fill color, only Font color, or keep both checked if you want to match a specific dual-color combination.