What Is SUM By Color In Google Sheets?
The SUM By Color in Google Sheets helps users check for the required colored cells and calculate the total of the numeric values in a dataset. We can also filter and total the cells highlighted in a specific color in the given range.
In the Google Sheets Sum by Color, users can apply the feature to find the total sum of cell values of the colored cells while analyzing sales reports and financial statements containing categories of data with different color codes.
For example, the table below lists students who have won medals for their team, where each team appears highlighted in a unique color. The number of medals won is given in column B. We have applied the filter feature for the headers, “Student” and “Team”. Let us find the total medals won using the sum by color w.r.t their team color.
Select cell E2, enter the formula =SUBTOTAL(9,B2:B11), press “Enter”. We get the output as 35 which is the total of 10 values.
Now, follow the path shown in the image below, i.e., “filter by color – cyan”. So, once we click on the required color, the corresponding cells in the chosen cell range get filtered and displayed. And we can view the total number of visible cells in the selected color in the target cell.
Then we get the output shown below. The sum of the cyan-colored cells is 15.
Key Takeaways
- The SUM By Color In Google Sheets methods help one to calculate the total number of colored cells in a given range based on the specified shade.
- Users can utilize the techniques to count colored cells in a range while working with financial figures in spreadsheets containing data categorized using colors which is helpful while generating reports according to the required colors.
- We can also perform the Conditional Formatting and then apply the Sum by color to calculate the required cell values.
- We can apply the Filter option with the SUBTOTAL function or the Power Tools from the Add-ons, to find the sum of colored numeric value cells in a given range.
How To SUM By Color In Google Sheets?
We can SUM By Color in Google Sheets using the following methods, namely,
- Using the SUBTOTAL Function.
- Using Add Ons.
Method #1 – Using the SUBTOTAL Function –
The steps to calculate the SUM by color using the Filters with SUBTOTAL() are as follows:
Step 1: Choose a cell range – select the “Data” tab – click the “Create a filter” option, as shown below.
In the selected cell range, the filters appear right next to the headers or first row of the selected cell range, as shown below.
Step 2: Next, select an empty cell and enter the formula =SUBTOTAL( and select the function_code as per the requirement, here 9, as we need to find the sum of the cells.
Step 3: Complete the formula. Write it as =SUBTOTAL(9, as shown in the syntax below.
Step 4: Enter the argument as cell values or references,close the formula brackets and press Enter. The SUBTOTAL() calculates the numeric values of the visible cells in the cell range and returns the output.
Step 5: Finally, we can click the required columns filter icon – select the “Filter by Color” option right-arrow – click the “Fill- Color” option right arrow à select the available color code options or required color, as shown below.
Once we click the required color, the corresponding cells get filtered in the chosen range. And the SUBTOTAL() in the result cell will get updated accordingly.
Likewise, we can utilize the Filter option to filter by the required color, one at a time, to find sum of the corresponding number of visible cells in the chosen cell range.
Method #2 – Using Add-Ons –
The steps to SUM By Color Using Add Ons are as follows:
Step 1: First ensure we have the needed Add-ons. Therefore, select the “Extensions” tab – click the “Add-ons” right-arrow – select the “Get add-ons” option, as shown below.
Step 2: The “Google Workspace Marketplace” window appears. There, type “Power Tools” in the search bar, select the software and install it.
Once the “Power Tools” software is installed, we will get the new option in the “Extensions” tab, as shown below.
Step 3: To calculate the sum of required colored cells in the dataset.
Select the dataset – select the “Extensions” tab – click the “Power Tools” right-arrow – select the “Start” option, as shown below.
Step 4: The “Power Tools” pane appears on the right.
- Click the “Calculate cells based on their colors” option drop-down.
- Select the “Function by color” option.
Step 5: Choose the options as follows:
- Select the “One Color” menu.
- Choose A1:C11 in the “Select range:” field.
- Choose the desired color in the “Select Color:” field, rest of the options, leave it as it is.
- From the “Use function:” drop-down, choose the “SUM” function.
- Select an empty cellfor the output in the “Paste results to:” field.
- Finally, click the “Insert function” button at the bottom of the “Power Tools” window.
We will get the sum of the selected colored cells in the output cell. If there is no dataset selected, then we will get an error, as shown below.
Examples
We will consider some SUM By Color In Google Sheets examples for the above-mentioned methods.
Example #1 – SUM By Color Using SUBTOTAL Function-
The dataset given below consists of fruits, their grades and quantity ordered. The rows of data are highlighted randomly. Let us calculate the SUM By Color In Google Sheets Using SUBTOTAL Function.
The steps to use the Filter method with SUBTOTAL() are as follows:
Step 1: Choose cells A1:C1 – select the “Data” tab – click the “Create a filter” option, as shown below.
In the selected cell range, the filters appear right next to the headers, as shown below.
Step 2: Choose cell F1 and enter the formula =SUBTOTAL(9,C2:C11), as shown below.
Step 3: Press Enter. The SUBTOTAL() returns the output, 2100, i.e., the total quantity of fruits, regardless of the color.
Step 4: Click the column C filter – select the “Filter by Color” option right-arrow – click the “Fill- Color” option right arrow à select the “dark green 1” color, as shown below.
Once we click the required color, the corresponding cells get filtered in the chosen range. And the SUBTOTAL() in cell F1 will return the updated sum of the filtered colored cells, as shown below.
Likewise, we can utilize the Filter option to filter by the required color, one at a time, to count the corresponding number of visible cells in the chosen cell range. For example, if we select the “Orange” color, then, we get the following output.
Example #2 SUM By Color Using Add Ons –
We will now SUM By Color in Google Sheets Using Add Ons using the dataset given below that consists of some smartphones and their quantity wherein the quantities in 20’s, 30’s and 50’s are colored respectively.
The steps to SUM By Color Using Add-on are as follows:
Step 1: Select cells A1:B7. Select the “Extensions” tab – click the “Power Tools” right-arrow – select the “Start” option, as shown below.
Step 2: The “Power Tools” pane appears on the right.
- Click the “Calculate cells based on their colors” option drop-down.
- Select the “Function by color” option.
Step 3: Choose the options as follows:
- Select the “One Color” menu.
- Choose A1:B7 in the “Select range:” field.
- Choose the “Lime” color in the “Select Color:” field, rest of the options, leave it as it is.
- From the “Use function:” drop-down, choose the “SUM” function.
- Select cell E1 for the output in the “Paste results to:” field.
- Finally, click the “Insert function” button at the bottom of the “Power Tools” window.
We will get the sum total of the green-colored cells in the output cell E1 as 9, as shown below, and we can see the applied formula by Add Ons, in the formula bar.
Likewise, we can test the method for other colors too. Unlike SUBTOTAL, where the results get updated, in ADD Ons we can get the results in desired cells.
Example #3
The May and June sales data of five employees are given below where each employee data if in a different color. We will sum by color using the SUBTOTAL() function.
The steps to calculate total sales of a single employee are,
Step 1: Choose cells A1:C1 – select the “Data” tab – click the “Create a filter” option, as shown below.
In the selected cell range, the filters appear right next to the headers, as shown below.
Step 2: Choose cell A9 and enter the formula =SUBTOTAL(9,A2:C6), as shown below.
Step 3: Press Enter. The SUBTOTAL() returns the output of all the employees both the months sales total, as shown below.
Step 4: Click any of the sales columns filter – select the “Filter by Color” option right-arrow – click the “Fill- Color” option right arrow – select the “light magenta 2” color, as shown below.
Once we click the required color, the corresponding cells get filtered in the chosen range. And the SUBTOTAL() in cell A9 will return the updated sum of the filtered colored cells, as shown below.
Important Things To Note
- We can SUM by color in Google Sheets using the SUBTOTAL() when the source cell range lies in one column, and the cells in the specified cell range contain numeric values.
- Ensure to install Add-ons, else we will be unable to perform the sum on colored cells using the Add-ons method.
Frequently Asked Questions (FAQs)
The SUBTOTAL in Google Sheets is inserted as follows:
Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Math” option right arrow – select the “SUBTOTAL” function, as shown below.
When we forget which category a function falls in, here SUBTOTAL, we can always use this alternate way to insert any function in Google Sheets as follows: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “ALL” option right arrow – select the “SUBTOTAL” function, as shown below.
However, the best option is to enter the formula manually.
A few reasons the SUM By Color in Google Sheets may not work are as follows:
a. The colored dataset is modified or deleted.
b. The right function_code is not selected in the “SUBTOTAL” function.
c. The Add-ons are not installed to perform the count of colored cells.
d. The data filters applied are removed. So, we are unable to filter by color.
Download Template
This article must help understand SUM By Color in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is SUM By Color In Google Sheets. We learn how to sum by colored cells in Google Sheets using Subtotal & add-ons with examples. You can learn more from the following articles. –
Delete Row Shortcut In Google Sheets
Leave a Reply