What Is Sum By Color In Excel?
The methods to sum by color in Excel enable one to add cell values based on a color in the required data range. And we can use the SUBTOTAL and GET.CELL functions to add cells by color.
Users can sum cell values by colors in Excel to add numeric data categorized in different shades in sales reports and financial statements.
For example, the following table lists students, and the project points they secured in different subjects.
Further, the project points of a student, Nicholas Robinson, are highlighted in Yellow in column C, which we must add and display the total in cell C18.
Then, we can use the sum by color in Excel formula in the required target cell to obtain the desired outcome.
In the above sum by color in Excel formula example, we first choose the target cell C18 and enter the Excel SUBTOTAL function.
As we must find the sum of the values in the colored cells, we set the first argument in the SUBTOTAL(), function_num, as SUM, indicated by the value 9. And then, we enter the range where the colored cells must be added, as the function’s second argument, ref1.
Thus, the SUBTOTAL() adds all the visible cells’ values in the specified range and returns 1332 as the total.
Next, we click the column C filter in Excel in the source dataset to access the filter options. And then, we select the Filter by Color option right arrow and the required color, Yellow.
The above action displays the column C cells highlighted in the chosen color, C5, C10 and C15. And the SUBTOTAL() in cell C18 shows the sum of the values in the abovementioned cells, 269, as the required total project points of the specific student, with the sum being by color.
The following section explains methods to sum by color in Excel without VBA, though VBA-based techniques are available.
Table of contents
Key Takeaways
- The techniques to sum by color in Excel help add cells in a column range based on the chosen or specified color. And if a column range includes cells in multiple colors, we can iterate the sum by color method for each color, one by one.
- Users can use the Excel sum by color option to add financial and statistical data highlighted in different shades in a dataset.
- We can use the SUBTOTAL function with the Filter option and the GET.CELL function with the SUMIF function to sum colored cells in a column range.
- We can sum cells in a column range by a specific font and foreground color.
How To Sum By Color In Excel?
We can determine the auto sum by color in Excel using the following methods:
- Sum by Color using SUBTOTAL Function
- Sum by Color using GET.CELL Function
Let us understand the steps involved in each technique with practical examples to avoid the scenario of the sum by color in Excel not working.
#1 – Sum By Color Using SUBTOTAL Function
The steps to apply the Filter option and use the SUBTOTAL() to auto sum by color in Excel are as follows:
- Select a cell in the source dataset and choose the Data tab – The Filter option to apply filters on the columns in the dataset.
- Select a cell to display the output.
- Type =SUBTOTAL( in the target cell. [Alternatively, type =S or =SUB and double-click the SUBTOTAL function from the Excel suggestions.]
- When we enter the opening bracket, Excel will show the functions from which we can choose the required function to apply to the required cell range. Choose 9 to select the Excel SUM function as the first argument value in the SUBTOTAL(). Next, enter a comma, and select the column range containing the colored cells we must add.
- Close the bracket. And then, press Enter to execute the SUBTOTAL().
- Click the Filter button of the column containing colored cells and select the Filter by Color option right-arrow in the Filter drop-down list. And then, select the color based on which we must add the cells.
And once we click the color, the SUBTOTAL() in the target cell will display the required sum of the visible colored cells’ values.
Basic Example
The following table shows smartphones and their order dates and unit ordered.
And the column C cells containing the units ordered data for the smartphone model Galaxy S10+ are highlighted in Yellow.
The task is to determine the total smartphone units ordered based on the colored cells in column C for a specific smartphone model and display the output in cell C13.
Then, we can use the SUBTOTAL() in the target cell and the Filter option in the source dataset’s column containing colored cells to sum by color in Excel without VBA.
Step 1: Choose cell A1. And then select Data – Filter or press Ctrl + Shift + L.
The filter applies to the source dataset columns.
Step 2: Choose cell C13 and enter the SUBTOTAL().
=SUBTOTAL(
Once we enter the opening bracket, Excel shows the function options to choose from as the first argument, function_num, value. And as we require to sum the colored cells’ values, double-click option 9 – SUM in the drop-down list.
Next, enter a comma.
=SUBTOTAL(9,
And then, enter the required column cell range containing the colored cells from the source dataset.
=SUBTOTAL(9,C2:C11
Next, enter the closing bracket, ‘)’.
=SUBTOTAL(9,C2:C11)
Step 3: Press Enter to execute SUBTOTAL() in the target cell C13.
The SUBTOTAL() adds the values in visible cells in the cell range C2:C11 and returns the sum, 1670.
Step 4: Click the column C filter and choose the Filter by Color option from the drop-down list.
And then, choose the required color, based on which we must add the cells in the chosen range.
Once we choose the required color, the corresponding colored cells get filtered in the chosen range. And the SUBTOTAL() in cell C13 will automatically return the sum of the filtered cells.
#2 – Sum By Color Using GET.CELL Function
The steps to sum by color using GET.CELL() are as follows:
- Ensure the cells in the required column in the source dataset are colored correctly.
- Add a column to the source dataset to update the color codes based on the colors used in the concerned column in the given dataset. And choose the first cell in the new column to set the heading.
- Select the second cell in the newly-added column and choose the Formulas tab à Define Name option.
- The New Name window opens. Please update the Name field with the required name. And then, enter the GET.CELL() formula in the Refers to field.
=GET.CELL(38,Cell Reference To Second Cell In Column Containing Colored Cells)
The first argument value in the function is 38. It refers to the color code data. And the second argument is the excel cell reference to the second cell of the column containing the colored cells. - Click OK to exit the New Name window.
- Choose the second cell in the newly-added column and enter the ‘=’ sign, followed by the defined name created in the previous two steps. Pressing Enter will give the color code of the color the cell is in the corresponding row in the column containing the shaded cells.
- Using the Excel fill handle, update the defined name in the remaining cells of the newly-added column to obtain the required color codes.
- Create a dataset, with the first column listing the unique color codes obtained for the source dataset. And we shall have the second column to show the sum by color values.
- Choose the first target cell in the second column of the new dataset. Enter the Excel SUMIF function to add cells in the source dataset column containing shaded cells by color based on the specified color code.
=SUMIF(Absolute Reference To Cell Range Containing All Color Codes, Cell Reference To Cell Containing A Unique Color Code, Absolute Reference To Cell Range Containing Colored Cells) - Press Enter to view the sum by color for the specified color code.
- Using the fill handle, update the SUMIF() in the remaining target cells to obtain the sum by color values based on the remaining color codes.
Basic Example
The following example will help add cells in multiple colors to ensure we avoid all scenarios of the sum by color in Excel not working.
The following table lists sales representatives and their region and July sales data.
And column C, containing the July sales data, has cells colored in different shades.
The task is to add column C cells by colors.
Then, the steps are as follows:
Step 1: We shall name column D as Color Code to list the color codes based on the colors of column C cells.
And then, choose cell D2 and Formulas – Define Name.
Step 2: The New Name window opens. We shall set the Name field as Color_Code and allow the defined name scope to be the current workbook.
Next, enter the GET.CELL() in the Refers to field.
=GET.CELL(38,’Sum By Color_Example2′!$C2)
And click OK to exit the window.
Step 3: Choose cell D2, enter ‘=’ and the first few characters of the required defined name.
Excel will list all the defined names and inbuilt functions starting with the specified phrase.
Next, double-click the required defined name, Color_Code, to choose it.
Step 4: Press Enter to obtain the color code of the corresponding column C cell color.
Based on the corresponding column C cell, the formula will update the column D cell with the foreground color as a number from 1 to 56. And if the column cell color is automatic (No Fill), the function returns 0.
Step 5: Using the fill handle, update the formula, applied via the defined name, in the remaining cells in column D.
Step 6: We shall create a dataset in the cell range G1:H5 to determine and display the sum by color output.
Choose cells G1 and H1 to update the column headings individually.
And then, update the unique color codes based on the values obtained in column D.
Step 7: Choose cell H2 and enter the SUMIF().
=SUMIF($D$2:$D$11,
The first argument is the absolute reference to the range containing all the color codes in the source dataset, followed by a comma.
Next, enter the cell reference to the unique color code specified in the second dataset, followed by a comma.
=SUMIF($D$2:$D$11,G2,
And then, enter the Excel absolute reference to the range containing colored cells in the source data.
=SUMIF($D$2:$D$11,G2,$C$2:$C$11
Finally, close the bracket to complete the formula.
=SUMIF($D$2:$D$11,G2,$C$2:$C$11)
Step 8: Press Enter to execute the SUMIF() in the target cell.
Step 9: Using the fill handle, update the SUMIF() in the remaining target cells.
Step 10: Choose cells H2:H5 and set the Home tab – Number Format option – Currency to view the output as currency values.
The SUMIF() checks for the specified color code in the range, entered as the first argument in the function. And then, the function adds the cells in the corresponding rows of the range, specified as the third argument in the function.
For instance, the SUMIF() in cell H2 checks for the color code 15 in the cell range D2:D11. And as 15 appears in cells D7:D8, the function adds the cells C7:C8 to return the sum by color value as $9,400.
Benefits
The benefits of sum cells by color in Excel are as follows:
- The option helps one to add statistical data categorized in different colors.
- The option helps deal with financial statements containing data sections indicated in unique colors in a better way.
- The option enables one to add highlighted data automatically and quickly.
Important Things To Note
- The SUBTOTAL()-based sum by color in Excel method enables one to sum cells by one color at a time in a range, with the range being only one column. Also, the colored cells must contain numeric values to ensure the function works.
- Ensure the SUBTOTAL()’s first argument value is 9 to enable the function to determine the sum of the required colored cells.
- The GET.CELL()’s first argument value should be 38. It ensures the function collects the color code data from the referenced cell, specified as the second argument in the function.
Frequently Asked Questions (FAQs)
We can sum by font color in Excel using the following steps, explained with an example.
The following table lists fruits and their grades and quantities.
And column C shows the grade C fruits’ quantities highlighted in Green font.
The task is to sum column C cells by font color and display the output in cell C23.
Then, the steps are as follows:
Step 1: Select cell A1. And then, choose Data – Filter to apply the filter to the source dataset columns.
Step 2: Choose cell C23 and enter the SUBTOTAL().
=SUBTOTAL(
Once we enter the opening bracket, Excel lists the functions we can choose as the first argument.
And as we must add the cell values, double-click the 9 – SUM option to choose it, followed by a comma.
=SUBTOTAL(9,
And then, enter the range containing the colored cells we must add, followed by the closing bracket.
=SUBTOTAL(9,C2:C21)
Step 3: Press Enter to execute the SUBTOTAL() in the target cell.
Step 4: Click the column C filter and select the Filter by Color right arrow. And choose the required color under Filter by Font Color.
The above action will display column C cells containing data in the chosen font color.
And then, the SUBTOTAL() in the target cell will automatically add the visible column C cells containing data in the chosen font color to give the required sum value.
You can sum and count by color in Excel using the following steps:
1. Ensure that cells colored in the source dataset are correct.
2. Select the first cell of the given dataset and choose Data – Filter to apply the filter to the source dataset columns.
3. Select a target cell and enter the SUBTOTAL().
Select 9 to set the SUM() as the first argument value in the SUBTOTAL(). Next, enter a comma, and choose the column range containing colored cells as the second argument.
4. Close the bracket. And then, press Enter to execute the SUBTOTAL().
5. Select another target cell and enter the SUBTOTAL().
Select 2 to choose the COUNT() as the first argument value in the SUBTOTAL(). Next, enter a comma, and choose the column range where we must count cells by color.
6. Close the bracket. And then, press Enter to execute the SUBTOTAL().
7. Click the Filter button of the column containing colored cells and choose the Filter by Color option right-arrow in the Filter drop-down list. And then, choose the color based on which we must add and count the cells.
And once we apply the filter, the SUBTOTAL() in the first target cell will display the sum by visible colored cells value. And the function in the second target cell will show the count by visible colored cells value.
Excel sum by color conditional formatting is possible. And the steps are as follows:
1. First, choose the required column range, where we must color cells to highlight them based on specific criteria.
2. Choose the Home tab – Conditional Formatting feature – New Rule option.
3. The New Formatting Rule window will open, where we must choose the last rule type to enter the required conditional formatting criterion.
4. Choose the Format button to access the Format Cells window. And then, set the required format for highlighting the required cells.
5. Click OK in the Format Cells and New Formatting Rule windows.
6. The cells where the conditional formatting criterion holds get highlighted in the chosen column range.
7. Finally, use the SUBTOTAL or the GET.CELL function method to sum cells by color to obtain the required output.
Download Template
This article must be helpful to understand the Sum By Color In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Sum By Color In Excel. Here we learn to sum by color in Excel using SUBTOTAL & GET.CELL functions with examples & benefits. You may learn more from the following articles –
Leave a Reply