Written by Allen Wyatt (last updated February 6, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
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:
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 Microsoft 365.
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!
Insert a row at the top of a range of cells, and the effects within your formulas may not match your expectations. This ...
Discover MoreWhen you enter references to cells in a worksheet, using the Fill Handle to propagate the formulas to other cells can be ...
Discover MoreDo you ever have a need to return just a few digits out of a number? This tip shows different formulas you can use to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments