Are you the type of Microsoft Excel user that loves to add a background cell color? The benefit of colored cells is that it makes important information stand out. Another useful 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. Best of all, you won’t need to write any VBA code or use the get.cell function.
What We’re Going to Build
In this tutorial, I’m going to convert a regular spreadsheet that has colored cell backgrounds into an Excel table. At the bottom of the table, I’ll add a TOTAL row that will adjust the column value based on a color selection. For example, if I filter by a green fill color, the column total will provide a different sum just for the green cells.
The reason I’m using an Excel table is that they incorporate some additional features. For example, you can easily 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 on 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, and so on.
Each math function is assigned a number. For example, “9” represents SUM. The second argument, Ref1, represents the first-named range to act on.
The 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?
Some people may be wondering why not use GET.CELL. This function allows you to 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 have to define a custom function in the Name Manager dialog and then 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 specific color code.
The other issue is that GET.CELL won’t work if you applied 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 background color.
- From the drop-down menu, select Filter by Color.
- In the Filter by Cell Color menu, choose your desired color.
Your table should have changed so 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 argument is [Volume]. These were both 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 certain 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 that represent 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 opt to build 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, the moment I type the left parentheses, I get a drop-down menu with the 22 choices.
My suggestion is to find the functions you’ll use most often and remember them. Microsoft also provides a table with values.
Change Function_num Values to COUNT Colored Cells
One benefit of SUBTOTAL is that by changing one value, you can have a completely different formula and result. For example instead of summing colored cells, what if I wanted to count colored cells? I would simply swap my “109” for “102”.
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 if you wish. 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 cells or sum cells, it can do much more. About the only limitation is you can only filter by one color value at a time.