Are you a Microsoft Excel user who loves adding a background cell color? The benefit of colored cells is that it makes important information stand out. Another helpful feature is that it’s easy to act on the colors. In this tutorial, I’ll show you how to sum colored cells in Excel. You won’t need to write any VBA code or use the get.cell function.
What We’ll Build Without Using VBA Code
In this tutorial, I will convert a regular spreadsheet with colored cell backgrounds into an Excel table. At the bottom of the table, I’ll add a TOTAL row to adjust the column value based on a color selection. For example, if I filter by green fill color, the column total will provide a different sum for the green cells.
I’m using an Excel table because they incorporate some additional features. For example, you can quickly sort a table by color. In addition, the Table Styles area allows you to add a TOTAL row using a checkbox. And while you might think the TOTAL row would be based on the SUM function, it uses the SUBTOTAL function.
SUBTOTAL VS. SUM
Most of us are familiar with SUM. It’s probably the first function we used because Microsoft kindly added the AutoSum button on the Excel ribbon. And if you highlighted various numeric cells, you could see the SUM displayed on Excel’s status bar at the bottom right.
The SUBTOTAL function does much more than sum cells or a range of cells. The function uses two additional arguments. The first argument is called Function_num, which does other math functions such as AVERAGE, COUNT, MAX, MIN, etc.
Each math function is assigned a number. For example, “9” represents SUM. The second argument, Ref1, represents the first-named range to act on.
Differences Between SUBTOTAL and SUM Functions
- SUBTOTAL only works on columns or a vertical range. SUM can work on either rows or columns.
- SUBTOTAL only acts on visible cells. If you can’t see the cell on your table, it won’t be counted unless you manually hid a row. SUM will not change its value based on filtering.
- SUBTOTAL can take additional function numbers.
- SUBTOTAL knows to skip other SUBTOTAL references in the column. SUM would double-count any SUBTOTAL in the column.
Why Not Use Get.Cell Function?
Some people may be wondering why not use GET.CELL. This function lets you get information about a workbook cell, such as the background color, font size, etc. However, you can’t access the function directly. In other words, I can’t type a formula with GET.CELL in the formula bar.
Instead, you must define a custom function in the Name Manager dialog and apply it to a named range. Then you need to add another column to hold the color index value. Finally, you’d have to use the SUMIF function for the color code.
The other issue is that GET.CELL won’t work if you apply a background color using conditional formatting.
How to Sum Colored Cells in Excel
The steps below show how to SUM colored cells using the SUBTOTAL function and an Excel table.
- Open a Microsoft Excel file where you’ve applied cell background colors (“fill color”) and column headers.
- Click any cell with data.
- From the Insert tab, click Table.
- On the Create Table dialog box, click the OK button.
- Make sure you tick the box for Headers.
- Click the Table Design tab in the Table Style Options section.
- Tick the checkbox for Total Row.
- Click the column header where you used the cell background color.
- From the drop-down menu, select Filter by Color.
- In the Filter by Cell Color menu, choose your desired cell color.
Your table should have changed so that only the rows containing your selected color cells show. In addition, the Total value on the bottom row should’ve changed. As you change your filter for different colored cells, you should see your Total change.
Understanding the SUBTOTAL Formula
Let’s step back a bit to see how the formula works. If you click the Total cell with your values, you’ll see the SUBTOTAL formula that was applied. On my test worksheet, I had a column called “Volume”. And Excel adds meaningful column references to the table headings.
In my case, the formula showed:
=SUBTOTAL(109,[Volume])
I have 2 arguments. The first argument is the “109,” and the second is [Volume]. These were added automatically when I ticked the Total checkbox in Table Styles. The second argument references my Volume column.
The “109” tells Excel to SUM the cells but ignore hidden rows. So, when I clicked the filter icon to reflect a specific color value, I was also hiding those rows where the cell background color didn’t meet my criteria.
The SUBTOTAL formula has 22 Function_num values representing 11 math functions and whether the formula should include hidden rows or ignore them. The first set of values, 1-11, will include hidden rows. In contrast, values 101-111 ignore hidden rows.
Finding SUBTOTAL Function_num Values
Although I find this function useful, I wish more information was displayed if you built your formula using the Formulas tab and Insert Function. If I use that method, there isn’t any tip as to the values I can enter in the Function Arguments dialog box.
In contrast, if I build the SUBTOTAL formula from the formula bar, I get a drop-down menu with 22 choices when I type the left parentheses.
I suggest finding the functions you’ll use most often and remembering them. Microsoft also provides a table with values.
How to COUNT Colored Cells
One benefit of SUBTOTAL is that you can have a completely different formula and result by changing one value. For example, instead of summing colored cells, what if I wanted to count colored cells? I would swap my “109” for “102”.
=SUBTOTAL(102,[Volume])
You Don’t Have to Use an Excel Table
I started this tutorial with a table because of the native functionality it provides. However, that is not a requirement. You can insert this Excel function in any cell and provide a different label. Moreover, you may prefer to use one of the other math functions.
Hopefully, you’ve seen the versatility of the SUBTOTAL function. While it can do things like count or sum cells, it can do much more. About the only limitation is you can only filter by one color value at a time.