Subtotal by Color: How to Do It

This article explains how to subtotal (sum) values by cell color in spreadsheet software like Microsoft Excel and Google Sheets. You will learn the two main methods—using a helper column with a color-detecting function, or using the built-in Subtotal tool after filtering by color—and get step-by-step instructions for both.

Formula

The core idea behind subtotaling by color is that spreadsheet functions like SUM or SUBTOTAL normally ignore cell formatting. To make them pay attention to color, you must either:

• Create a helper column that assigns a numeric value to each color, then use SUMIF on that helper column.
• Filter by color and use the SUBTOTAL function, which only sums visible cells.

In Excel, the helper column uses a VBA user-defined function. In Google Sheets, you can use a custom function with Google Apps Script. The formula for the helper column is:

=GetColorValue(A1) (Excel VBA) or =getColorValue(A1) (Google Sheets)

This function returns a number (e.g., 1 for red, 2 for yellow) based on the cell’s fill color. Then you use =SUMIF(helper_range, 1, value_range) to sum only the cells with that color.

For the filter method, the formula is simply:

=SUBTOTAL(109, value_range)

where 109 tells the function to sum only visible cells (after filtering by color).

Worked Example

Suppose you have a list of sales amounts in column B (B2:B10) and some cells are highlighted yellow to indicate “priority” sales. You want the total of only the yellow cells.

1. Add a helper column C. In C2, enter the custom function =GetColorValue(B2) (or =getColorValue(B2) in Sheets). Copy down to C10.
2. Assume the function returns 1 for yellow and 0 for no fill. Your C column will show 1s and 0s.
3. Use =SUMIF(C2:C10, 1, B2:B10) to get the sum of yellow cells.

If your values are: 100 (yellow), 200 (no fill), 150 (yellow), 300 (no fill), the SUMIF returns 250 (100+150).

For the filter method: select your data, go to Data > Filter, then use the filter arrow on column B, choose “Filter by color” and pick yellow. Then use =SUBTOTAL(109, B2:B10) which will return 250 as well.

Why Subtotal by Color Is Not Built-In

Spreadsheets treat cell formatting as presentation, not data. The SUM function only looks at cell values, not at whether a cell is red or bold. That is why you cannot write =SUMIF(A1:A10, "red", B1:B10) directly. To subtotal by color, you must first convert the color information into a value that formulas can read, or use a filter to hide non-matching rows.

This design is intentional: it keeps formulas stable even if you change formatting. But it means that color-based subtotaling requires a workaround.

Method 1: Helper Column with VBA (Excel)

In Excel for Windows, you can create a simple VBA function:

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module and paste this code:

Function GetColorValue(cell As Range) As Integer
    GetColorValue = cell.Interior.ColorIndex
End Function

3. Close the editor. Now you can use =GetColorValue(A1) in a helper column. This returns the color index (e.g., 6 for yellow, 3 for red).
4. Then use =SUMIF(helper_range, 6, value_range) to sum yellow cells.

Note: This function does not update automatically if you change a cell’s color. You must re-run the macro or press Ctrl+Alt+F9 to recalculate.

Method 2: Google Sheets Custom Function

Google Sheets uses Apps Script. Go to Extensions > Apps Script, paste this code, and save:

function getColorValue(cell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(cell);
  return range.getBackground().replace('#', '');
}

This returns the hex color code (e.g., ‘ffff00’ for yellow). You can then use =SUMIF(helper_range, "ffff00", value_range). The function updates when the sheet recalculates, but not instantly on color change; you may need to edit a cell to trigger it.

Method 3: Filter by Color + SUBTOTAL

This method does not require any code and works in both Excel and Google Sheets:

1. Select your data range and turn on filtering (Data > Filter).
2. Click the filter arrow on the column that has the colored cells.
3. Choose “Filter by color” (Excel) or “Filter by condition > Custom formula” (Sheets, but Sheets does not have native filter by color—you need to use a helper column for that). In Excel, pick the color you want.
4. Now use =SUBTOTAL(109, value_range) to sum only the visible cells.

This approach is quick and does not need programming, but it only works in Excel (Google Sheets lacks native color filtering).

Choosing the Right Method

• If you need a permanent, formula-based solution that updates when colors change, use the helper column method.
• If you just need a one-off total and you are in Excel, the filter + SUBTOTAL method is fastest.
• If you are in Google Sheets, the custom function is the only way to get a formula that reads color.

Consider the maintenance: VBA and Apps Script may be blocked by your organization’s security policies. In that case, the filter method (Excel) or a manual helper column (typing 1s and 0s) are your fallbacks.

Common Mistakes and Limitations

• Using SUM instead of SUBTOTAL after filtering—SUM ignores the filter and sums all cells.
• Forgetting that VBA functions do not auto-recalculate when you change a fill color—you must force a recalculation.
• In Google Sheets, the custom function returns a hex code, not a simple number. You need to match the exact hex code, which can vary if you use theme colors.
• Color-based subtotals are fragile: if someone changes a cell’s fill color, your subtotal may silently become wrong.
• If you have more than one color, you need a separate SUMIF for each color.

Also note that conditional formatting colors are not read by these functions—they only see the cell’s actual fill, not the formatting applied by a rule.

Frequently Asked Questions

Can I subtotal by color without using VBA or scripts?

Yes, in Excel you can use the filter by color feature and then the SUBTOTAL function. This does not require any code. In Google Sheets, there is no native filter by color, so you would need to manually create a helper column or use a script.

Why does my SUMIF return 0 when using the VBA color function?

Most likely because the color index returned does not match the number you used in SUMIF. For example, yellow might be 6 in one workbook but 27 in another if you used a custom color. Check the helper column values to see what numbers are actually returned.

Does the subtotal by color method work with conditional formatting?

No. Conditional formatting applies formatting based on rules, but the actual cell fill is still “no fill” or the original color. The VBA function reads the cell’s interior color, not the conditional formatting result. You would need to replicate the conditional formatting logic in a formula instead.

How do I sum multiple colors at once?

Use multiple SUMIF formulas and add them together. For example: =SUMIF(helper, 6, values) + SUMIF(helper, 3, values) sums yellow and red cells. Or use SUMPRODUCT with an array of color indices.

Is there a way to make the subtotal update automatically when I change a cell’s color?

In Excel, you need to force a recalculation with Ctrl+Alt+F9 or by editing any cell. In Google Sheets, you can edit a cell or change the sheet’s settings to recalculate on change. There is no true real-time update because color changes do not trigger recalculations.

Can I use this technique in Excel Online or Google Sheets mobile?

Excel Online does not support VBA macros, so the helper column method will not work there. Google Sheets mobile does support custom functions, but you need to set up the script on a desktop first. The filter method works in Excel Online, but Google Sheets mobile has no filter by color.

Related

• Steam Deck OLED vs LCD: Which Display Is Better for Gaming?
• Budget Headphones with Mic for Calls, Music & Gaming
• IT Blocks File Sync: What It Means & How to Fix It

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2