Welcome to this tutorial where I’m going to show you how to calculate subtotals by group using the SUMIFS function in Excel and Google Sheets. It’s a convenient way to group and total data based on specific categories or groups.
Table of subtotals by category or group
In this example, we will use the UNIQUE function and the SUMIFS function to automatically calculate the number of products per product group.
Start by adding the UNIQUE function to cell E3: =UNIQUE(B3:B11).
Once this formula is entered, a list will be automatically created below the cell to display all the unique values found in the “Product Group” column.
Next, use the SUMIFS function to calculate the total products per product group: =SUMIFS(C3:C11,B3:B11,E3).
Table of subtotals by category or group – Without the UNIQUE function
If you are using a version of Excel that does not have the UNIQUE function, you can use the INDEX function, the MATCH function, and the COUNTIF function to create an array formula that will produce a list of unique values from a range of cells.
{=INDEX($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))}
Once this formula is entered, you will get a list of unique values in the “Product Group” column.
Next, use the SUMIFS function again to calculate the total products per product group: =SUMIFS(C3:C11,B3:B11,E3).
Adding subtotals in a data table
Another way to calculate subtotals by category or group is to directly add subtotals in a data table using the IF and SUMIFS functions.
=IF(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3))
This formula uses the nested SUMIFS function within an IF function. It compares the value of the “Product Group” column in each row with the previous row. If they are the same, it displays an empty cell. If they are different, it displays the sum of the products.
Sorting data by group
If your data is not already sorted by product group, you can use Excel’s sorting function to organize them.
Simply select the data table, click on “Sort A to Z,” and your data will be sorted by product group.
Locking cell references
To make your formulas more readable, it is recommended to lock the appropriate cell references. For example:
=IF(B3=B2,"",SUMIFS($C$3:$C$11,$B$3:$B$11,B3))
This ensures that your formulas work correctly when copied and pasted into other cells in your file.
Using pivot tables to display subtotals
Another method to calculate subtotals is to use pivot tables. Pivot tables allow you to automatically summarize data and display totals and subtotals in different ways.
The formulas also work in Google Sheets
These formulas work the same way in Google Sheets as they do in Excel. However, it’s worth noting that the UNIQUE function is case-sensitive in Google Sheets.
You can also check out Crawlan.com for additional tips and tutorials on Excel and Google Sheets.
Now you know how to calculate subtotals by category or group in Excel and Google Sheets. Use these tips to organize and analyze your data more efficiently!
