If you need to compute the total for certain cells based on their font or fill color, you may have noticed that Excel formulas operate on stored values, not displayed values. That means that functions such as SUM and SUMIFS operate on the underlying cell values and disregard cell formatting, such as font or fill color. This post provides three steps to workaround this issue and compute a total based on fill color.
Before we get too far, let’s have a look at what we are trying to accomplish.
We have a range of data and selected transactions have been highlighted with a cell fill color, as shown below.
We’ll want to be able to highlight additional rows any time, and we don’t want to rewrite or update our formula when we do. That means our formula range needs to include the amount column and we are precluded from using a formula that picks and chooses each cell (like E10+E12+E15…).
When we try to write a formula that adds up the amount column but includes only the highlighted transactions, we get stuck. Regardless of which summing function we use, we can’t seem to tell Excel to include only the highlighted cells. This is because formulas operate on the underlying stored values and disregard the cell formatting.
So, can we do this? Yes, we can, no worries.
We can accomplish our objective with the help of two Excel items, namely, a filter and a SUBTOTAL function.
Step 1: The filter. We can filter by font or fill color using the built-in filter feature of Excel. To turn on filters, simply select any cell within the data range and then the following Ribbon icon:
- Data > Filter
This will turn on little filter controls, or drop-downs, in the header row. These are shown below.
With them, we can filter by fill color. Simply pull one down, and then select Filter by Color. A slide-out menu will appear showing the colors used within the data region. When you pick a color, Excel will show those rows and hide the others, as demonstrated below.
Once we have the filter working, we are just about done. All we need to do is write a formula that includes only the visible rows.
Step 2: The blank row. Since we always want our total row to be visible, we don’t want it included within the filter range. To prevent Excel from hiding the total row when applying a filter, we want to skip a row before writing the formula. So, we won’t write our formula in the row immediately under the data range, we’ll leave a blank row in between the last data row and the formula row.
Step 3: SUBTOTAL. Excel has several functions that can add stuff up. To accomplish our objective, we can’t use a standard SUM function because it includes both visible and hidden rows. The SUBTOTAL function on the other hand, includes only visible rows within a filtered region. The trick to using the SUBTOTAL function is to set the first argument to either 9 or 109. Either would work in this case because we want to sum the results and we are using a filter to hide the rows. If our amount column was stored in the range E8:E19, then we would use the following formula in our total row:
This is pictured below.
Connecting the dots then…we know we can use the filter to show only the highlighted transactions, we know the blank row between the last data row and the formula will help ensure that our total row is excluded from the filter, and we know the SUBTOTAL function will include only the visible rows within the filtered region. So, will this work? Let’s find out…
Yes, it worked! The total updates from 7,560 (all rows) to 2,377 (highlighted rows)…we have computed a total based on fill color just like we wanted!
If you have any other fun techniques for summing by color, or other suggested uses for filters, please share by posting a comment below…thanks!
- Download the Excel file: SumByColor
- This technique works if the data is stored in a table as well. Instead of writing the SUBTOTAL formula manually, Excel will add it automatically when we check the Total Row checkbox on the TableTools ribbon tab. Plus, the filter controls will automatically be included in the table’s header row.
- If your total row is being included the the filter (and thus hidden) be sure you have a blank row between the last data row and the formula row and then turn off filters (Data > Filter) and then back on again (Data > Filter).