Three Ways to Sum Cells by Color in Excel Using Formulas, Macros, and Filters

Sometimes, you may want to sum up values in a range of cells based on their background color. For example, you may have a sales report where the cells are colored according to the product category, and you want to calculate the total sales for each category. However, Excel does not have a built-in function to sum cells by color. In this article, we will show you how to use the SUMIF function with some tricks to achieve this task.

The SUMIF function in Excel allows you to sum values in a range of cells that meet a certain criterion. The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

where:

• range is the range of cells that you want to evaluate based on the criteria.
• criteria is the condition that determines which cells to sum. It can be a value, a cell reference, or an expression.
• sum_range is the range of cells that you want to sum. If omitted, Excel will use the same range as the first argument.

For example, the formula =SUMIF(A2:A10,“>10”,B2:B10) will sum the values in B2:B10 if the corresponding values in A2:A10 are greater than 10.

However, the SUMIF function does not accept cell color as a valid criterion. Therefore, we need to use some tricks to assign a value to each cell color and use that value as the criterion for the SUMIF function.

Procedures

There are different methods to sum cells by color in Excel, but we will focus on three of them:

• Using a helper column with the GET.CELL function
• Using a VBA macro with the SumColor function
• Using a filter with the SUBTOTAL function

Using a helper column with the GET.CELL function

The GET.CELL function is a hidden function in Excel that can return various information about a cell, such as its format, address, value, etc. One of the information that it can return is the color index, which is a unique number assigned to each cell color. However, the GET.CELL function cannot be entered directly as a worksheet function. Instead, it has to be used within a named range.

To use this method, follow these steps:

1. Select a range of cells where you want to insert a helper column. For example, if your data is in A2:C10, select D2:D10.
2. Click the Formulas tab, then select Name Manager. This will launch the Name Manager dialog box.
3. Click New. In the New Name dialog box, enter a name for the named range, such as “Color”. In the Refers to bar, enter the formula =GET.CELL(38,Sheet1!C2), where 38 is the argument for the color index, and Sheet1!C2 is the reference to the first cell in the data range that has the color you want to sum. You can change the sheet name and the cell reference according to your data.
4. Click OK, then Close. You have created a named range that returns the color index of the cell C2.
5. Select the first cell in the helper column, D2, and enter the formula =Color. This will return the color index of the cell C2, which is 6 in this example.
6. Copy the formula in D2 and paste it to the rest of the cells in the helper column. This will fill the column with the color index of each cell in the data range.
7. Now, you can use the SUMIF function with the helper column as the range and the color index as the criterion. For example, to sum the values in B2:B10 if the corresponding cells in C2:C10 have the color index 6, enter the formula =SUMIF(D2:D10,6,B2:B10).

Using a VBA macro with the SumColor function

Another method to sum cells by color in Excel is to use a VBA macro that creates a custom function called SumColor. This function takes two arguments: the range of cells that you want to sum, and a sample cell that has the color you want to sum. The function will loop through the range and check the color index of each cell. If the color index matches the sample cell, the function will add the cell value to the total sum.

To use this method, follow these steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Click Insert > Module. This will create a new module in the Project Explorer window.
3. In the code window, paste the following VBA code:

Function SumColor(SumRange As Range, ColorCode As Range) Dim ColorCodeValue As Integer Dim TotalSum As Long ColorCodeValue = ColorCode.Interior.ColorIndex Set rCell = SumRange For Each rCell In SumRange If rCell.Interior.ColorIndex = ColorCodeValue Then TotalSum = TotalSum + rCell.Value End If Next rCell SumColor = TotalSum End Function

4. Close the Visual Basic Editor. You have created a custom function that can sum cells by color.
5. In your worksheet, enter the formula =SumColor(SumRange,ColorCode), where SumRange is the range of cells that you want to sum, and ColorCode is a cell that has the color you want to sum. For example, to sum the values in B2:B10 if the corresponding cells in C2:C10 have the same color as F2, enter the formula =SumColor(B2:B10,F2).

Using a filter with the SUBTOTAL function

The third method to sum cells by color in Excel is to use a filter with the SUBTOTAL function. The SUBTOTAL function can perform various calculations on a range of cells, such as sum, count, average, etc. The advantage of the SUBTOTAL function is that it can ignore hidden rows or columns in the calculation. Therefore, by applying a filter to the data based on the cell color, we can use the SUBTOTAL function to sum only the visible cells.

To use this method, follow these steps:

1. In your worksheet, enter the formula =SUBTOTAL(9,SumRange), where 9 is the function number for the SUM function, and SumRange is the range of cells that you want to sum. For example, to sum the values in B2:B10, enter the formula =SUBTOTAL(9,B2:B10). This will return the sum of all the cells in the range.
2. Select any cell in the data range, and click the Filter icon on the Data tab. This will apply a filter to the data and show drop-down arrows on the header cells.
3. Click the drop-down arrow on the header cell of the column that has the color you want to sum. For example, if you want to sum the cells based on the color in column C, click the drop-down arrow on cell C1.
4. Expand the Filter by Color option, and select the color that you want to sum. For example, if you want to sum the cells that have a yellow background, select yellow. This will filter the data and show only the rows that have the selected color in the column.
5. The SUBTOTAL function will automatically update and show the sum of the visible cells that have the selected color.