Summing Cells Using a Particular Background Color

by Allen Wyatt
(last updated February 6, 2021)

1

Peter has columns of dollar amounts that customers owe. When a customer pays, he highlights that cell in yellow by filling the cell with a yellow background color. Peter wonders if there is a way to sum only the cells that are highlighted in a particular column.

There are a few ways you can approach this problem, each of which I'll address in the following sections.

Filter and Use SUBTOTAL

A simple two-pronged approach is to filter your data based on the color you want to sum. Then, once filtered, you can rely on the results returned by the SUBTOTAL function.

In order to filter your data according to color, follow these steps:

  1. Make sure your data has column headings defined.
  2. Select a cell within your data.
  3. Display the Data tab of the ribbon.
  4. Click the Filter tool in the Sort & Filter group. Excel places a drop-down arrow next to the column headings.
  5. Click the drop-down arrow next to the heading of the column containing the colored cells. You'll see a number of filtering options displayed.
  6. Click the By Color drop-down list. Different color options appear, depending on the characteristics of your cell formatting.
  7. Hover the mouse pointer over the Cell Color option and then click on the color you want to use in your filtering. In Peter's case, this would be Yellow. Excel immediately filters the data by the selected color.
  8. Make sure that only the FALSE value has a check mark next to it.
  9. Click in a cell in the data table. This dismisses the filtering options first visible in step 5.

At this point you can enter a formula (in any cell you want) that will return the desired sum. Assuming that you want a sum of the values in C2:C93, you would use the following:

=SUBTOTAL(9,C2:C93)

This works because the SUBTOTAL function sums only values that remain visible after filtering. In this case, it means that only the yellow cells are summed.

Revamp Your Data

There is a very strong argument to be made for taking a look at how your data is organized in the worksheet. Applying a static color to cells isn't a terribly robust method of marking a row as "paid." A better way would be to add a column next to the amount column and use that new column to indicate if the amount has been paid. Then, if desired, you could use Conditional Formatting to highlight the values using any color you want, based on what you entered in the new column.

How does help when you need a sum of the colored cells? Simple—you aren't basing the sum on the color (applied via Conditional Formatting), but on the value in the column next to the amount. For instance, let's say that your amounts are in column A, so in column B you enter the letter "Y" if the adjacent amount has been paid. Now all you need to do is to use the following formula to determine the total of all the lines marked as paid:

=SUMIF(B:B,"Y",A:A)

The advantage to reorganizing your data in this manner is that your information about what has been paid—and what hasn't—is immediately "accessible" to other formulas and can even be exported to other programs. (You cannot export the color of a cell to another program.)

Use a Macro

The final approach to getting the sum you want is to use a macro. It is best to implement a user-defined function that can be accessed from your worksheet. This one will look at a specified range of cells and return the sum of any cell that is filled with yellow:

Function SumYellow(rTarget As Range)
    Dim c As Range
    Dim dTotal As Double

    Application.Volatile
    dTotal = 0
    For Each c In rTarget
        If c.Interior.Color = vbYellow Then
            dTotal = dTotal + c.Value
        End If
    Next c
    SumYellow = dTotal
End Function

In order to use the UDF in your worksheet, you would enter something similar to the following, assuming that the range containing the values is A2:A93:

=SumYellow(A2:A93)

If you think it is possible that a cell in the specified range might contain a text value instead of a numeric, then you should modify the macro just a bit:

Function SumYellow(rTarget As Range)
    Dim c As Range
    Dim dTotal As Double

    Application.Volatile
    dTotal = 0
    For Each c In rTarget
        If c.Interior.Color = vbYellow Then
            If IsNumeric(c) Then dTotal = dTotal + c.Value
        End If
    Next c
    SumYellow = dTotal
End Function

This version checks whether the cell contains a value or not before adding it to the dTotal variable. If the cell contains a non-numeric value, then it is skipped entirely.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13346) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Printing AutoCorrect Entries

If you want to print a list of all the AutoCorrect entries in your document, Word doesn't provide a method. You can use ...

Discover More

Two Printed Copies to Different Paper Trays

Many modern printers include multiple paper trays that can be used for different types or colors of paper. Word allows ...

Discover More

Converting to ASCII Text

When you work with imported or pasted data in an Excel worksheet, you may see some strange looking characters at times. ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Determining a State from an Area Code

Want to be able to take information that is in one cell and match it to data that is contained in a table within a ...

Discover More

Locating a Single-Occurrence Value in a Column

Given a range of cells containing values, you may have a need to find the first value in the range that is unique. This ...

Discover More

Tracing Errors

Sometimes it can be confusing to figure out the source of an error that is displayed in your worksheet. Excel provides a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four minus 4?

2021-02-07 04:37:53

Muhammad Adnan Shakil

Dear Sir,

This function isn't working.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.