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.

Objective

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!

Notes

  • 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

10 comments:

  1. Best Excel Tutorial
    Reply

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

  2. Sandrine
    Reply

    Love it!

  3. Robin
    Reply

    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
      Reply

      Glad this will help save some time 🙂

  4. Ирина
    Reply

    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.

  5. Pamela Roberts
    Reply

    My only thing…i have 4000 rows that have to be displayed but only want to total the highlighted cells. Not have the total to change to accept all cells as demonstrated above.

    1. Jeff Lenning Post author
      Reply

      Pamela
      If all 4000 rows need to be displayed, and you want the total of a subset (the highlighted rows) then one option is to use a helper column with SUMIFS. For example, apply a temporary filter to display the highlighted rows. Then enter a stored value such as TRUE or Yes in the helper column for all visible (highlighted) rows. Then you can remove the filter to display all rows. At this point, you can use SUMIFS (or a PivotTable) to compute the sum based on the stored values in the helper column. I have a blog post that demonstrates the SUMIFS function in case it would help. https://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/

      Thanks
      Jeff

  6. Dave
    Reply

    Very helpful, I’m looking to do this with SUMPRODUCT, thus a very similar problem but with fixed values being multiplied by by a quantity in an adjacent column, Ideally using a macro.

  7. Alice Hall
    Reply

    I have a spreadsheet that contains paid and accrued totals in same column, as shown below. The accrued amounts are in red (representing negative $$) and the paid $$ are regular print (black). I want to write a formula that will automatically adjust the bottom line total if additional $$ (red or black) are added to a column. In other words, I want the formula to automatically adjust by color.
    AMRI-07 October $1,640.94
    AMRI-08 November $1,236.57
    AMRI-09 December $1,131.30
    AMRI-10 January $1,486.40
    AMRI-12 March $2,626.23
    AMRI-13 Apri $1,304.03
    Accrual May -2,922.39 (color red)
    Accrual June -12,774.67 (color red)
    Accrual July-Sept -19,000.00 (color red)
    In other words, if I add negative $$, the figure will automatically appear in red.

    1. Kurt LeBlanc
      Reply

      Hey Alice!

      First, you would ideally have the numbers in an Excel Table, so that the formatting will apply to any additional rows in that column. Then you just need to adjust the number format to whatever you want. Highlight the column then click the format dialog box. (I prefer the keyboard shortcut Ctrl+1!) On the number tab, you’ll see numerous options, including even a custom format! You probably only need the currency or accounting option, and choose the black with the red negative number.

      Hope that helps:)
      Kurt LeBlanc

Leave a Reply

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