Sum by Color in Excel

How to Sum by Color in Excel? (2 Useful Methods)

The top 2 Methods to Sum by Colors in Excel are as follows –

  1. Usage of SUBTOTAL formula in excelSUBTOTAL Formula In ExcelThe Excel Subtotal function is a built-in function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range.read more and filter by color function.
  2. Applying GET.CELL formula by defining the name in the formula tab and applying the SUMIF formula in excel to summarize the values by color codes.

Let us discuss each of them in detail –

You can download this Sum by Color Excel Template here – Sum by Color Excel Template

#1 – Sum by Color using Subtotal Function

To understand the approach for calculating the sum of values by background colors, let us consider the below data table, which provides details of amounts in US$’s by region and month.

Sum by color in Excel

Lets start.

  1. Suppose we would like to highlight those cells which are negative in values for indication purpose, this can be achieved by either applying conditional formatting or by highlighting the cells manually, as shown below.


    Sum by color in Excel Example

  2. Now, to achieve the sum of cells that are colored in excel, enter the formula for SUBTOTAL below the data table. The syntax for the SUBTOTAL formula is shown below.


    Sum by color in Excel Example1
    The formula that is entered to calculate the summation is

    =SUBTOTAL(9,D2:D13)

    Here number ‘9’ in the function_num argument refers to sum functionality, and the reference argument is given as the range of cells to be computed. Below is the screenshot for the same.

    Sum by color in Excel Example1 -2

  3. As seen in the above screenshot, a summation of the USD amount has been calculated in order to compute the amounts highlighted in a light red background color. Apply filter to the data table by going to Data then selecting a filter.


    Sum by color in Excel Example1 -3

  4. Then select filter by color and choose the light red cell color under ‘Filter by cell color.’ Below is the screenshot to better describe the filter.


    Sum by color in Excel Example1 -4

  5. Once the excel filter has been applied, the data table will be filtered for only light red background cells, and the subtotal formula applied at the bottom of the data table would display the summation of the colored cells, which are filtered as shown below.


    Sum by color in Excel Example1 -5

    As shown in the above screenshot, the computation of the colored cell is achieved in cell E17, subtotal formula.

#2 – Sum by Color using Get.Cell Function

The second approach is explained to arrive at the sum of the color cells in excel, as discussed in the below example. Consider the data table to better understand the methodology.

Sum by color in Excel Example 2
  • Step 1: Now, let us highlight the list of cells in the USD Amount column, which we are willing to arrive at the desired sum of colored cells, as shown below.
Sum by color in Excel Example 2-1
  • Step 2: As we can see in the above screenshot, unlike in the first example here, we have multiple colors. Thereby we will be using the formula =GET.CELL by defining it within the name box and not directly using it in excel.
  • Step 3: Now, once the dialog box for the ‘Define Name’ pops up, enter the Name and the formula forgetting.CELL in ‘Refer to’ as shown in the below screenshot.
Sum by color in Excel Example2-4

As seen in the above screenshot, the Name entered for the function is ‘CellColor’ and the formula =GET.CELL(38,’Example 2!$D2) is to be entered in ‘Refers to.’ Within the formula, the numeric 38 refers to the cell code information, and the second argument is the cell number D2 refers to the reference cell. Now click, OK.

  • Step 4: Now enter the function Name ‘CellColor’ in the cell beside the color-coded cell, which was defined in the dialog box, as explained in step 3.
Sum by color in Excel Example2-5

As can be seen in the above screenshot, the function ‘CellColor’ is entered, which returns the color code for the background cell color.

Example2-6

Similarly, the formula is dragged for the entire column.

Example2-8
  • Step 5: Now, to arrive at the sum of the values by colors in excel, we will be entering the SUMIF formula. The syntax for the SUMIF formula is as follows:-
sumif formula
Example2-9

As can be seen from the above screenshot, the following arguments are entered into the SUMIF formula:-

  • The range argument is entered for cell range E2: E13.
  • The criteria are entered as G2, whose summarized values are needed to be retrieved.
  • The range of cells is entered to be D2: D13.

The SUMIF formula is dragged down for all the color code numbers for which values are to be added together.

summations

Things to Remember

  • While using the approach by SUBTOTAL formula, this functionality allows the users to filter for only one filter color at a time. Moreover, this functionality can be used to add only one column of values by filter colors. If there is more than one column with different colors by rows in the different columns, the SUBTOTAL may only show the correct result for one filter by color to a specific column only.
  • The GET.CELL formula, in combination with the SUMIF approach, eliminates this limitation, as this functionality could be used to summarize by colors for multiple color codes in the cell background.

Recommended Articles

This has been a guide to Sum by Color in Excel. Here we discuss how to find Sum by Colors in excel using 1) Subtotal Function and 2) Get. Cell, along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *