How to Excel Sum by Color – Use SUBTOTAL and GET.CELL Formula

As you know Excel is fully loaded with advanced features, however, some functions cannot be performed directly. Excel sum by color is a function that does not have a direct way to execute. However, it does not mean that you cannot perform this function.

You can perform this function using multiple other features available in Excel. When you don’t have a built-in feature in a tool, you have to find another way for it. Here we have two main tricks that can be used for Excel sum by color. These methods are:

1. The SUBTOTAL formula in Excel
2. GET.CELL formula with SUMIF formula in Excel

The SUBTOTAL formula is a built-in way to perform the relevant calculation. For the Excel sum by colour function, you will use the SUBTOTAL formula and filter by color sum function. And the other method includes GET.CELL formula is used for defining the name and then applying the SUMIF formula, to sum up, the values by color codes.

Let’s move on to the details of these two methods:

• Using SUBTOTAL and AutoFilter to Sum by Color in Excel

Using both features for a single function will help you understand how to sum by color in Excel. Follow the steps given below:

1. First of all, you have to select the whole data table.
2. Open the Data ribbon.
3. Click on the Filter command.
4. On the Price column header, you will see a drop-down icon, click on it.
5. From this drop-down menu, choose the Filter by Color option.
6. Click on the blue color rectangle.
7. Choose the cell C16 to keep the formula results.
8. Now type this formula: =SUBTOTAL(109, D5:D7) in the cell.
9. Press the ENTER button to finish the procedure.

This is it. The desired results are in front of you.

• Using GET.CELL to Sum by Color in Excel

In the second most important method, you will get to know a secret formula. In this method, GET.CELL function is used that help in getting the color index value of colored cells. The color index value is helpful when used with the SUMIF formula for the sum of cells with a specific color. Though GET.CELL is not a new function, however, it is effectively used for compatibility reasons.

Here you will see a dataset of colored cells to be used for the sum.

First of all, a named range will be created to make use of GET.CELL function, so that you can give the color value of colored cells.

Follow the steps:

1. From the Ribbon, choose the Formulas tab.
2. Choose the “Name Manager” option given in the Defined Name Group.
3. Select the New option from the Name Manager dialog box.
4. Now, type the Name – SumColor in the “New Name dialog box.”
5. Put this formula in the Refers to the field.

=GET.CELL (38,$B2)

6. Press OK to close the Name Manager Dialog box.

Following the above steps will let you create a named range that can be used in the workbook. Do you know that GET.CELL function had two arguments?

A first argument is a number that explains what we need and the second argument is the cell reference of that cell itself.

Let’s move on to the further procedure:

In column B, get all the color index values of all the colors. Follow the steps given below to proceed further.

1. Add the header – Color Index in cell C1.
2. Put this formula in cell C2: =SumColor
3. Now, you will have to use the same formula for all the cells in column C. you can copy-paste cell C2 or use the Fill handle.

SumColor is the named range that uses the GET.CELL function to find the color index value. Remember that you should always start using the formula from the second cell in the column. Doing this will let you have a separate number for each color and it can be used to get the SUM of cells according to their color.

Let’s understand this procedure with some easy-to-do steps:

1. In cells E2 and E3, you may have to give the cells the color for which you needed the sum value. We have orange color in cell E3 and yellow color in cell E2.
2. Put this formula in cell F2: =SUMIF(C2:C15, SumColor,B2:B15)
3. In cell F3, copy that cell and paste it here.

Following all the above-mentioned steps will give you the sum colored cells in Excel. The desired results are given in column F. SUMIF formula allows you to add all the values in column B. Undoubtedly, the second method is lengthy but it will give you exactly what you want.

To Sum Up

In this post, we have covered two major methods that let you understand how to Excel sum by color. As you know practice makes a man perfect. So, you will have to follow the steps vigilantly and keep practicing them. Also, do share the knowledge with others because one candle can lighten up a hundred candles. You can be that one candle.

All clear now, how to sum in Excel by color is not rocket science at all. Keep trying!