Google Sheets Group Sum

What Is Google Sheets Group Sum?

Thetechniques to Google Sheets group sumenable us to add the values of the given dataset based on a group or category. We can sum values by group using the inbuilt functions such as UNIQUE along with SUMIF.

Users can utilize the sum by groupmethods in Google Sheets to determine the total inventory level based on a specific product and the total sales based on a cited department at a firm.

For example, the following dataset contains the tasks completed data of a set of teams at a firm.

The aim is to list the unique teams in column E. Next, we must sum the task completed values based on the unique teams.

Then, following the definition of the Google Sheets group sum explained earlier, we can use the UNIQUE() and SUMIF() to secure the required output.

We achieve the required output using Google Sheets group sum, as explained below.

Select cell E2 and enter the UNIQUE(), with the range A2:A13 containing the team names in the source dataset supplied as its input. The function applies as an array formula, like array formulas in Excel, that updates all the unique teams in the chosen cell E2 and the range below it. Since the range A2:A13 contains three unique teams, A, B, and C, the UNIQUE() returns A, B, and C as the output in cells E2:E4.

Next, we choose cell F2 and enter the SUMIF(). The function accepts three inputs. The first argument is the range A2:A13 holding the teams in the source dataset. Next, the second is the reference of the cell holding the team, based on which we must add the task completed values. Finally, the third argument is the range C2:C13 holding the tasks completed data in the source dataset.

Thus, the function searches for the cells in the range A2:A13 where the team is A, which are cells A2, A5, A8, and A11. Next, it groups the tasks completed values in the range C2:C13 by team A, which are the corresponding cells C2, C5, C8, and C11 values. Thus, it sums the grouped cell values to return the total tasks completed data for team A in cell F2, 181.

Likewise, we can apply the formula as the Google Sheets group sum command in the remaining target cells F3:F4 to obtain the required data for the remaining teams. Please note that the ranges supplied as the first and third arguments to the SUMIF() are the same in all the target cells, while only the second argument keeps varying with the team.

What Is Google Sheets Group Sum?

The Google Sheets group sumis a method to add a set of values categorized based on the specified group.
The formula to sum values by group in Google Sheets is useful to determine values such as the total sales generated by a specific employee and the total units of a cited stationery item sold in a year.
We can use the UNIQUE function or the INDEX-MATCH formula to list the unique values, based on which we must group the numeric values to add. Next, we can use the SUMIF() or SUMIFS function to add the numeric values based on the cited group.

Explanation And Usage

Explanation

In some scenarios, the source dataset may contain a list of items, with the items repeating multiple times in the list. Further, we might be required to add numeric data based on distinct items considered as groups or categories.

In such a scenario, we can use the UNIQUE() or INDEX-MATCH function in Google Sheets (similar to INDEX-MATCH function) to achieve the list of unique groups. Next, we can use the SUMIF or SUMIFS, similar to SUMIFS Excel function, to sum the numbers cited in the source dataset based on the specified distinct group.

Usage

The formula to sum values by group in Google Sheets is helpful for evaluating values such as the total annual sales at a firm based on a cited branch office. We can also use the formula to determine the total revenue generated at a company based on a specific item category.

How To Sum Values By Group In Google Sheets?

The steps to sum values by group, in line with the definition of Google Sheets group sum explained earlier, are as follows:

1. Select a cell, enter the following UNIQUE(), and press Enter.

=UNIQUE(range)

The range argument value is the cell range containing a list of values, from where we must update the list of unique values in the target cell and the range below it. Please note that the UNIQUE() executes as an array formula, returning an array of values.

2. Select the cell in the same row as the target cell chosen in Step 1 in the adjacent column. Next, enter the following SUMIF(), and press Enter.

Where,

range: The range that we aim to test against the specified condition.
criterion: The test condition to apply to the specified range.
- If the range argument holds the text to verify against, the criterion argument value should be a string. Furthermore, the criterion argument value can hold wildcard characters (‘?’ representing a single character or ‘*’ representing 0 or continuous characters. However, if we must match a ‘?’ or ‘*’, place the Tilde character (‘~’) before the specific character (‘~?’ and ‘~*’). Please note that the string condition should be within double quotations. Every cell in the cited range is then tested against the specified criterion for a match.
- If the range argument value holds numbers to test against, the criterion argument value may be a string or a numeric value. When a number is cited, every cell in the specified range is tested for a match with the criterion argument value. On the flip side, the criterion argument value can be a string holding a number or a number with any of the below operators before it: = (checks if the range cell value equals the criterion value), > (checks if the range cell value exceeds the criterion value), or < (checks if the range cell value is below the criterion value)
sum_range: The range of values to add when different from the specified range.

The above formula will add a set of values from the sum_range, based on the group, determined by matching the cited criterion argument value with the range values.

3. Apply the SUMIF() formula mentioned above, as the Google Sheets group sum command, in the adjacent column’s cells against all the unique values.

Examples

Let us check out the examples explaining how to sum values by group using Google Sheets group sum technique.

Example #1 – Sum Group-Wise In Google Sheets

The source dataset lists fruits, their categories and the units sold data.

The requirement is to list the unique fruits in column E based on the source dataset. Next, we sum the units sold values, given in the source dataset, based on the unique fruits in column F.

Step 1: Select cell E2 and enter the UNIQUE().

=UNIQUE(A2:A13)

Press Enter.

The function executes as an array formula. It lists the distinct fruits in the range E2:E5, Apple, Orange, Banana, and Watermelon, based on the range A2:A13 in the source dataset.

Step 2: Select cell F2 and enter the SUMIF().

=SUMIF($A$2:$A$13,E2,$C$2:$C$13)

Press Enter.

Step 3: Select cell F2. Next, using the fill handle, similar to Excel fill handle, update the formula in cells F3:F5.

Let us check the cell F5 formula to understand the working.

The SUMIF() accepts three arguments. The first one is the range A2:A13, containing the list of fruits in the source dataset. The second is the address of cell E5 holding the fruit Watermelon, based on which we must group the units sold data in the source dataset. Next, the third argument is the range C2:C13, holding the units sold data in the source dataset.

So, the function searches for the cells in the range A2:A13 holding the fruit Watermelon, which are cells A6, A8, and A11. Next, it adds the units sold values in the corresponding rows of column C, which are the cells C6, C8, and A11 values, to return 515 as the required total units sold value.

Example #2 – Sum Group-Wise In Google Sheets Using Combination Formula

The following dataset lists a set of stationery items, their order numbers and costs.

The requirement is to update the total order cost of the stationery item cited in cell F1 and showcase the output in cell F2.

Firstly, ensure the stationery item specified in cell F1 is present in the source dataset. Otherwise, we may face the issue of the formula of Google Sheets group sum returning 0 as the output in cell F2.

Step 1: Select cell F2, enter the SUMIF(), and press Enter.

=SUMIF(ARRAYFORMULA(FIND(F1,A2:A13)),1,C2:C13)

Firstly, we confirm that the item A4 Sheets in cell F1 is in the list of items cited in column A of the source dataset, avoiding the scenario of the Google Sheets group sum returning 0 in cell F2.

Next, here is how the SUMIF() in cell F2 works.

Firstly, the ARRAYFORMULA() containing the FIND(), which works like the Excel FIND function, returns an array of 1s and #VALUE! error value, similar to #VALUE! error in Excel. The array value will be 1 when the cell value in the range A2:A13 matches the cell F1 item. On the flip side, the array value will be the #VALUE! error value when the cell value in the range A2:A13 does not match the cell F1 item.

Next, the SUMIF() finds the match for the criterion value of 1 in the array range the ARRAYFORMULA() returns, which are the 4^th, 7^th, and 10^th values. Next, the SUMIF() adds the order cost values in the corresponding positions in the range C2:C13, which are the C5, C8, and C11 values, and returns $13,500 as the required output.

Example #3 – Sum Group-Wise In Google Sheets Without UNIQUE Function

The source dataset lists products and their date-wise inventory levels.

We should list the unique products in column E based on the source dataset. Next, we must determine and showcase the total inventory level of each distinct product in the corresponding cells of column F.

Step 1: Select cell E2, enter the INDEX-MATCH(), and press Enter.

=INDEX($A$2:$A$13,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$13),0))

Next, select cell E2 and utilize the fill handle option to implement the formula in the remaining column E cells.

The INDEX-MATCH() enables us to determine the list of distinct products without using the UNIQUE().

First, the COUNTIF(), which is similar to Excel COUNTIF function, returns an array of 1s and 0s. The array value will be 1 when the cell value in the second argument range is any of the values in the specified first argument range. Otherwise, the array value will be 0.

Next, the MATCH(), which works like the Excel MATCH function, finds an exact match for the value of 0 in the array the COUNTIF() returns. It returns the position of the first match. Finally, the INDEX(), which works like the Excel INDEX function, returns the value in the cell, indicated by the position the MATCH() returns, from the cited range.

Step 2: Select cell F2, enter the SUMIFS(), and press Enter.

=SUMIFS($C$2:$C$13,$A$2:$A$13,E2)

Next, choose cell F2 and, utilizing the fill handle option. Implement the function in the rest of the corresponding column F cells.

The SUMIFS() function logic is similar to the SUMIF(). It accepts three arguments. The first is the range containing the values we must add based on a group. The second is the range where we must find the cells to secure the required group. Next, the third argument is the condition to check against the values of the second argument range. It is to determine the required group.

In this case, the SUMIFS() checks for cells holding the product, cited in the corresponding column E cell, in the column A range. Once it identifies the required column A cells, it adds the values in the same rows of column C to return the total inventory level grouped by product.

Important Things To Note

When we use the UNIQUE() for theGoogle Sheets group sum, we might get rows of data that may appear as duplicates. In such cases, ensure the text values in the source cells do not have varying hidden data.
The SUMIF(), which we use along with the UNIQUE(), can conduct conditional additions based on only one condition. However, when there are multiple conditions involved, we can use the DSUM().

Frequently Asked Questions (FAQs)

How to sum by group using Pivot table in Google Sheets?

We can sum by group using Pivot table in Google Sheets, as explained below, with an illustration.
The source dataset holds the grocery list, the item categories and their costs.

We must find the total cost based on each distinct grocery item.
Step 1: Click on one cell in the source dataset and select Insert 🡪 Pivot table.

The Create pivot table box appears, which shows the source dataset range in the first field, Data range.

We must update the target cell address in the second field. We should mention whether we want to display the pivot table in the new or current sheet. In this case, we choose to show the pivot table in cell E1 of the existing or current sheet.

Next, click Create in the box to view the Pivot table editor pane on the right of the workspace, with the empty pivot table displayed in the cited target cell.

Step 2: We need to group by the grocery items to add the cost values. So, place the cursor on the Grocery Item option under Search in the pane. Next, drag the cursor to the Rows category in the pane.

Since we must add the cost values, place the cursor on the Cost ($) option under Search in the pane. Next, drag the cursor to the Values category in the pane.

The above action will result in the following pivot table. We see the total cost values based on the different grocery items.

How to sum values by group by a certain condition in Google Sheets?

We can sum values by group by a certain condition in Google Sheets using the QUERY() along with SUM().
The generalized formula is as follows:

=QUERY(source data_range,”select column_containing categories, SUM(column_containing values to add based on a category) group by column_containing categories”)

How to perform a case-insensitive sum by group in Google Sheets?

We can perform a case-insensitive sum by a group in Google Sheets using the SUMIF function.

Download Template

This article must be helpful to understand Google Sheets Group Sum, with its formula and examples. You can download the template here to use it instantly.

Google Sheets Group Sum Download

Google Sheets Group Sum