Summing Filled Cells

Written by Allen Wyatt (last updated October 15, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

Tiffany has a column that contains numeric values. Some of the cells have been filled with different colors. Some of those colors are explicit; some are applied through conditional formatting. She would like to be able to sum the cells that contain any fill color, regardless of the color. In other words, the sum should exclude only the cells that have no fill color.

One way to accomplish this task is by using the filtering capabilities of Excel. For instance, you could follow these steps:

  1. Select a cell in your data column.
  2. Display the Data tab of the ribbon.
  3. Click the Filter tool. Excel places drop-down arrows in the first cell of the column.
  4. Click the drop-down arrow. Excel displays a palette of filtering options.
  5. Click the arrow at the right of the By Color field. Excel displays three options: Fill Color, Cell Color, and Cell Icon.
  6. Hover the mouse pointer over the Fill Color option. Excel displays a list of the colors used in the column.
  7. Choose the No Fill option. Excel immediately filters the data so that only those rows with no fill color in that column are displayed.
  8. In a different cell, enter the following formula:
=SUM(A2:A99)-SUBTOTAL(9,A2:A99)

The value returned from the formula is the sum of all the cells that use any fill color. This works because the SUM function returns of sum of all the cells in the range (in this case, A2:A99), and the SUBTOTAL function returns the sum of all the visible cells in the range. Subtract the visible (non-filled) cells from the total for the range, and you end up with the sum of all filled cells.

If you would prefer to not filter your data, then you could create a user-defined function to return the sum. The following would work great for this purpose:

Public Function SumFilled(Target As Range)
    Dim cell As Range
    Dim sAddr As String

    Application.Volatile
    For Each cell In Target
        sAddr = cell.Address(External:=True)
        If Evaluate("CellHasFill(""" & sAddr & """)") Then
            SumFilled = SumFilled + WorksheetFunction.Sum(cell)
        End If
    Next cell
End Function
Private Function CellHasFill(Addr As String) As Boolean
    CellHasFill = (Range(Addr).DisplayFormat.Interior.ColorIndex <> xlColorIndexNone)
End Function

Note that there is a UDF defined here (SumFilled) and a function that is called from the UDF. This arrangement is necessary because the heart of the macro is the .DisplayFormat.Interior.ColorIndex property which returns the color that is displayed in a cell, regardless of whether it is the result of an explicit fill or of a conditional format. This property—most likely because of a quirk (i.e., bug) in Excel—won't work directly in a UDF, though it will work in a hard-coded (non-UDF) macro. For more information on this and why this workaround approach was used, see the following thread at SubStack:

https://stackoverflow.com/questions/52265819/getting-cell-interior-color-fails-when-range-passed-from-worksheet-function/

Thus, to use the UDF you could put the following in your worksheet:

=SumFilled(AA2:A99)

If you want to use a non-UDF macro, then the following will work:

Public Sub ClrSum()
    Dim x As Double
    
    Set Rng = Range("A2:A99")
    x = 0
    For Each c In Rng.Cells
        If c.DisplayFormat.Interior.ColorIndex <> xlColorIndexNone Then x = x + c.Value
    Next c
    MsgBox x
End Sub

Run the macro, and it evaluates all the cells in the range A2:A99 and returns the sum displayed in a message box.

Finally, it should be noted that if you have the opportunity to rethink the organization of your worksheet, it would be advisable to not use color as data. In other words, you might consider adding a column to contain a numeric indicator as to the status of the values in your presently color-coded values. In this way you could easily use functions such as SUMIF to evaluate your data. If you want the color coding, then you could create conditional formatting that would modify color based on the value in the added status column.

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 (9859) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

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

Messed-up Typing

It is not uncommon for newcomers to Word to overwrite their existing document text as they are editing. There is a reason ...

Discover More

Sorting Text as Numbers

When you are sorting by text values, Excel can be very literal, which may not get you the sorting that you want. This tip ...

Discover More

Preserving the Undo List

The undo list can be a lifesaver when working in a macro. Unfortunately, the undo list is not preserved when you run a ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Adding a Missing Closing Bracket

When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. ...

Discover More

Counting Wins and Losses

Need to count the number of W (win) or L (loss) characters in a range of cells? You can develop a number of formulaic ...

Discover More

Averaging Based on Multiple Criteria

When you need to determine an average based on a very small selection of cells from a large dataset, based on multiple ...

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 3 + 7?

2022-10-17 09:52:03

J. Woolley

My Excel Toolbox includes array function HasFill(Target), where Target must be a range array like A1:C3 or a single cell like D4. HasFill returns a TRUE/FALSE array the same size as Target indicating whether each cell in Target has fill (explicit or conditional). Utilizing HasFill, here is a formula to sum cells in A2:A99 that contain fill:
=SUMPRODUCT(--HasFill(A2:A99),A2:A99)
The double unary operator converts from TRUE/FALSE to 1/0.
Here is an abbreviated version of HasFill:

Public Function HasFill(Target As Range) As Boolean()
    Dim A() As Boolean, sAddr As String
    Dim nRows As Long, nCols As Long, nR As Long, nC As Long
    Application.Volatile
    With Target.Areas(1)
        nRows = .Rows.Count
        nCols = .Columns.Count
        ReDim A(1 To nRows, 1 To nCols)
        For nR = 1 To nRows
            For nC = 1 To nCols
                sAddr = .Cells(nR, nC).Address(External:=True)
                A(nR, nC) = Evaluate("CellHasFill(""" & sAddr & """)")
            Next nC
        Next nR
    End With
    HasFill = A
End Function

Notice HasFill utilizes Private Function CellHasFill, which is included in the Tip. If Target is not contiguous like (A1:C3,D4), only the first contiguous area applies.
See https://stackoverflow.com/a/52265973/10172433
and https://sites.google.com/view/MyExcelToolbox/


2022-10-15 10:55:49

J. Woolley

This Tip has some issues:
1. '=SumFilled(AA2:A99)' should be '=SumFilled(A2:A99)'
2. In Sub ClrSum:
2.a. Might add 'Dim Rng as Range, c as Range'
2.b. Change '...Then x = x + c.Value' to
'...Then x = x + WorksheetFunction.Sum(c)' or
'...Then x = WorksheetFunction.Sum(x,c)'
for the same reason as Function SumFilled; otherwise, a non-numeric or text cell value might cause trouble.


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.