Sum by Color

Excel SUBTOTAL function by Jeff Lenning

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.

Highlighted rows by Jeff Lenning

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.

How To

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.

Excel filters by Jeff Lenning

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.

Excel filter by color by Jeff Lenning

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:

=SUBTOTAL(9, E8:E19)

This is pictured below.

Excel SUBTOTAL function by Jeff Lenning

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…

Sum by Color by Jeff Lenning

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).

This article was written by Jeff Lenning


  1. Best Excel Tutorial

    Clever 🙂 Thanks for this one. It was fun reading your post.

  2. Sandrine

    Love it!

  3. Robin

    This is great! I use color all the time and, before this trick, laboriously selected the cells to sum. Great time saver.

    1. jefflenning Post author

      Glad this will help save some time 🙂

  4. Ирина

    This image depicts that here we don’t need the total sum of all the elements but instead we want the sum of elements that have the same background color.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.