# 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

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

Application.Volatile
For Each cell In Target
If Evaluate("CellHasFill(""" & sAddr & """)") Then
SumFilled = SumFilled + WorksheetFunction.Sum(cell)
End If
Next cell
End Function
```
```Private Function CellHasFill(Addr As String) As Boolean
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

Viewing Footnotes

Adding footnotes to a document is essential for some types of writing. How you view those footnotes depends on the ...

Discover More

Inserting the Document Revision Number

Need to know how many times your document has been saved? Word keeps track of this information, and makes it easily ...

Discover More

Stop Graphics and Text from Jumping Around

Do you struggle with getting your graphics and surrounding text to appear just the way you want it? Here are some ...

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)

Getting a Conditional Count of Cells Containing Values

Excel provides several worksheet functions that can be used to count cells containing values--"particularly numeric ...

Discover More

Determining a Zodiac Sign from a Birthdate

If you want to find out the Zodiac sign for a birthdate, there are a number of ways you can do it. This tip provides ...

Discover More

Incrementing References by Multiples when Copying Formulas

You can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied ...

Discover More
##### Subscribe

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

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 8 - 4?

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

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.