Sum by Color

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).
Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

11 Comments

  1. Best Excel Tutorial on January 21, 2016 at 2:05 pm

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

  2. Sandrine on February 3, 2016 at 1:24 pm

    Love it!

  3. Robin on February 3, 2016 at 9:14 pm

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

    • jefflenning on February 4, 2016 at 12:25 pm

      Glad this will help save some time 🙂

  4. Ирина on March 31, 2016 at 2:50 pm

    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 on April 10, 2017 at 1:33 am

    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.

    • Jeff Lenning on April 21, 2017 at 7:27 pm

      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. http://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/

      Thanks
      Jeff

  6. Dave on June 21, 2017 at 8:50 pm

    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 on July 26, 2017 at 11:10 am

    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.

    • Kurt LeBlanc on August 7, 2017 at 7:36 am

      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

  8. TIM WEAKLAND on March 20, 2019 at 12:46 am

    Is there a way to sum conditional formatted cells that have color? I have a spreadsheet that across the column headers are weekly dates, and then below that, sales data. When a product is on sale, it is highlighted in yellow, using conditional formatting.

    I would like to just add up the yellow cells. This will encompass 52 columns by years end.

    Thanks
    Tim

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.