If you need to find the sum of a set of values in Microsoft Excel, you can use the SUBTOTAL function. This function takes as its input a range of cells and returns the sum of all the values in that range. To use the SUBTOTAL function, first select the cells that you want to include in your calculation. Next, enter the SUBTOTAL function into Excel’s formula bar and press Enter. The function will return the sum of all the values in your selected cells. If you want to exclude certain values from your calculation, you can do so by using the exclusion criteria option when entering the SUBTOTAL function into Excel’s formula bar. For example, if you want to calculate the sum of all numbers between 1 and 10 but not any numbers that are greater than 10, you would enter =SUBTOTAL(1:10,“not Greater Than”) into Excel’s formula bar. ..
Obtaining subtotals for groups of related items is easy in Microsoft Excel. In fact, you have two ways to do this. You can either use the SUBTOTAL function or the Subtotal feature, whichever works best. We’ll show you how.
What Is a Subtotal in Excel?
As mentioned, SUBTOTAL in Excel allows you to group the same or related items in a list and use a function to calculate the values. For example, you can use it to sum sales by month or average grades by student. You could also add inventory by product or count the number of bills due next week.
RELATED: How to Use the COUNT Function in Microsoft Excel
Using the SUBTOTAL function by creating a formula or using the Subtotal feature, you can get the calculations you need in just a few steps.
Use the Excel SUBTOTAL Function
You can create a formula using the Excel SUBTOTAL function with the flexibility to include or exclude rows you’ve hidden.
The syntax for the formula is SUBTOTAL(function_number, reference 1, reference 2,…) where the first two arguments are required. You can use additional cell references or named ranges for the remaining arguments as needed.
The function_number argument allows you to insert one of 11 functions using its corresponding number. The first 11 include hidden rows, while the second 11 exclude them. So, simply insert the number for the function you need and how you want to handle the hidden rows.
As an example of the SUBTOTAL function, we’ll sum the total of sales in the cell range B2 through B4 using this formula:
For this next example, we hid rows 4 and 5. Using this first formula, we can obtain our sum using the number 9 for the first argument to include the hidden data.
Now, we’ll exclude those hidden rows from our total, using the number 109 for our first argument.
Once you have your subtotals, you can use the SUBTOTAL function once more for a grand total at the bottom. Alternatively, you can use the SUM function to add the subtotals.
RELATED: How to Sum a Column in Microsoft Excel
For another example, we’ll use the average function to include hidden rows 3 and 4 with this formula:
And next, we’ll exclude the hidden rows with this formula:
Notes on the SUBTOTAL Function
Here are a few things to keep in mind when using the SUBTOTAL function in Excel:
The function works for columns or vertical ranges, not rows or horizontal ranges. If you have nested subtotals in the reference argument area, Excel ignores these in the subtotal result. When you use a filter, the data filtered out is excluded from the subtotal result.
Use the Subtotal Feature
Another way to use the SUBTOTAL function in Excel is by using the Subtotal feature. This way, you can automatically add calculations and group the items at the same time. Excel uses the SUBTOTAL function to accomplish this for you. Let’s look at a couple of examples.
RELATED: How to Calculate Workdays With a Function in Microsoft Excel
Using the Subtotal feature for our sales by month, we can group the data per month and sum each group with a grand total at the bottom.
Select all of the rows you want to group and subtotal. Go to the Data tab and pick “Subtotal” in the Outline drop-down menu.
When the Subtotal box opens, choose how you want to add the subtotals.
At Each Change In: Select the column you want to use for the grouping. Here, we picked Month so that a new group is created when the Month changes in the sheet. Use Function: Select the function you want to use, such as sum, average, minimum, maximum, or another option. For our example, we selected Sum. Add Subtotal to: Check the box for where you want the subtotal to calculate. For our example, we picked Sales.
Optionally check the boxes at the bottom for the additional items as you like. Click “OK.”
You’ll then see your data update to group and subtotal the rows and create a grand total at the bottom. Use the plus, minus, and number buttons to collapse or expand the groups for easier viewing.
As another example, we’ll use student grades. We’ll use the Subtotal and Group features to display an average grade for each student. Here’s the process.
Select the rows, go to Data, and pick “Subtotal” in the Outline drop-down menu.
In the Subtotal box, we’ll choose Student in the change drop-down list and Average in the function list. We’ll then check the box for Grade. Click “OK.”
We now have our rows grouped by student with an average grade for each and an overall average at the bottom. Again, you can use the buttons on the left to collapse and expand the groups.
If you decide to ungroup the rows after you use the Subtotal feature, the rows return to normal. However, those containing the SUBTOTAL function used by Excel remain for you to continue using or simply delete if you prefer.
The Subtotal feature can become complex if you plan to use many groups. However, these basic examples should help get you started if you’re interested in this function.
One thing to note is that you can’t add an Excel table subtotal with this feature. If you have your data in a table, you can either insert the Excel formula for SUBTOTAL as described earlier or convert your table to a cell range to use the feature. If you choose the latter, you’ll lose the table functionality.
Now that you know how to insert subtotals in Excel, check out how to remove duplicate rows.
