How do I sum a list in Google Sheets?

This tutorial will demonstrate how to calculate subtotals by group using the SUMIFS Function in Excel and Google Sheets.

Subtotal Table by Category or Group

First, we will demonstrate how to create a dynamic subtotal summary table from a data range in either Excel 365 onwards or Google Sheets.

We use the UNIQUE Function and the SUMIFS Function to automatically subtotal the Number of Products by Product Group:

To create this subtotal table, we use the standard application of the SUMIFS Function to sum the Number of Products that match each Product Group. However, before this is possible, we need to create a list of unique Product Groups. Microsoft Excel 365 and Google Sheets users have access to the UNIQUE Function to create a dynamic list of unique values from a cell range. In this example, we add the following formula to cell E3:

When this formula is entered, a list is automatically created below the cell to show all unique values found within the Product Group data range. In this example, the list extended itself to cover E3:E5 to show all 3 unique Product Group values.

This is a dynamic array function where the size of the results list does not need to be defined, and it will automatically shrink and grow as the input data values change.

Note that in Excel 365, the UNIQUE Function is not case sensitive, but in Google Sheets it is. Consider the list {A; a; B; c}. The UNIQUE Function output depends on the program:

• {A; B; c} in Excel 365
• {A; a; B; c} in Google Sheets

If you are using an Excel version before Excel 365, you will need to take a different approach. This is discussed in the next section.

Subtotal Table by Category or Group Pre Excel 365

If you are using a version of Excel before Excel 365, the UNIQUE Function is not available for use. To replicate the same behavior, you can combine the INDEX Function and MATCH Function with a COUNTIF Function to create an array formula to produce a list of unique values from a range of cells:

In order for this formula to function, the fixed cell references need to be written carefully, with the COUNTIF Function referencing the range $E$2:E2, which is the range starting from E2 until the cell above the cell containing the formula.

The formula also needs to be entered as an array formula by pressing CTRL + SHIFT + ENTER after it has been written. This formula is a 1-cell array formula, which can then be copy-pasted into the cells E4, E5 etc. Do not enter this as an array formula for the whole range E3:E5 in one action.

In the same way as in the previous example, a SUMIFS Function is then used to subtotal the Number of Products by Product Group:

Sum by Category or Group Subtotals in Data Tables

As an alternative to the summary table method shown above, we can add subtotals directly into a data table. We will demonstrate this by using the IF Functions along with the SUMIFS Function to add a Subtotal by Group to the original data table.

This example uses a SUMIFS Function nested within an IF Function. Lets break down the example into steps:

To add summary statistics directly into a data table, we can use the SUMIFS Function. We start by totaling the Number of Products that match the relevant Product Group:

This formula produces a subtotal value for every data row. To show subtotals only in the first data row of each Product Group, we use the IF Function. Note that the data must already be sorted by Product Group to ensure that the subtotals are displayed correctly.

The IF Function compares each data rows Product Group value with the data row above it, and if they have the same value it outputs a blank cell [].

If the Product Group values are different, the sum is displayed. This way, each Product Group sum is displayed only once [on the row of its first instance].

Sorting Datasets by Group

If the data isnt already sorted, we can still use the same formula for the subtotal.

The dataset above isnt sorted by Product Group, so the Subtotal by Group column displays each subtotal more than once. To get the data into the format we want, we can select the data table and click Sort A to Z.

Locking Cell References

To make our formulas easier to read, weve shown some of the formulas without locked cell references:

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

Read our article on Locking Cell References to learn more.

Using Pivot Tables to Show Subtotals

In order to remove the requirement to pre-sort the data by Product Group, we can use the power of Pivot Tables to summarize the data instead. Pivot Tables calculate subtotals automatically and display totals and subtotals in several different formats.

Sum by Category or Group in Google Sheets

These formulas work the same in Google Sheets as in Excel. However, the UNIQUE Function is case sensitive in Google Sheets.

Video liên quan