Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Excel Sum by Color
The summation by colors in excel is essentially a crucial activity when we have a specific cell highlighted in colored backgrounds. This becomes difficult to summarize or add the values by colors in order to get the exact values for the respective color-coded cells. This requirement does not have a direct formula to arrive at the desired result, thereby it is necessary to make a workaround to achieve the appropriate result. In this topic, we would discuss and solve a few examples to learn how to sum by color in excel and the methodologies used to get the desired solution.
Top 2 approaches to Sum by Colors in Excel are as follows –
- Usage of SUBTOTAL formula and filter by color function.
- Applying GET.CELL formula by defining the name in formula tab and applying SUMIF formula in excel to summarize the values by color codes.
#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.
Step 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.
Step 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 SUBTOTAL formula is shown below.
The formula that is entered to calculate the summation is
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.
Step 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.
Step 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.
Step 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.
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 is discussed in the below example. Consider the data table to better understand the methodology.
Step 1: Now let us highlight the list of cells in USD Amount column which we are willing to arrive at the desired sum of colored cells as shown below.
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.
This function returns the information that is related to the particular cell based on the basis of the number entered in the type_num argument.
The syntax for the GET.CELL is as follows:-
The arguments in the formula are defined below:-
- Type_Num: Numeric data is entered in this argument based on the type of information needed from the specific cell.
- Reference: This argument is required, and the cell reference is entered to retrieve the information needed.
The GET.CELL formula is entered in ‘define name’ window located in ‘Formula’ ribbon. The purpose of this formula is to return the color index number of the respective background cell color.
We need to go to the Formula tab and select ‘Define Name’ as shown below.
Step 3: Now once the dialog box for the ‘Define Name’ pops up, enter the Name and the formula for GET.CELL in ‘Refer to’ as shown in the below screenshot.
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.
As can be seen in the above screenshot, the function ‘CellColor’ is entered which returns the color code for the background cell color.
Similarly, the formula is dragged for the entire column.
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 SUMIF formula is as follows:-
The details for the argument are:
- Range: This argument requires the range of the cells against which we want to apply the criteria. In this case the range of color code cells.
- Criteria: The cell reference whose values are required to be summarized.
- [sum_range]: The range of cells to be entered whose values are required to be added together.
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 who’s 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.
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 are more than one columns with different colors by rows in the different column the SUBTOTAL may only show the correct result for one filter by color to specific column only.
- The GET.CELL formula in combination with SUMIF approach eliminates this limitation, as this functionality could be used to summarize by colors for multiple color code in the cell background.
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 –