How to Calculate SUBTOTAL in Excel? 2 Useful Methods

(Note: This guide on how to calculate SUBTOTAL in Excel is suitable for all Excel versions including Office 365)

SUBTOTAL is a function in Excel that helps you to calculate the average, sum, product, and other functions for the elements in the table.

Imagine that you are the sales manager of a particular sales firm. Your responsibility will be to keep track of the number of sales made in each division. If the data were less, keeping track of them would have been feasible. When there are a lot of divisions and units, keeping track of the accumulated sales report might be an exhausting task. In such cases, what do you do?

This is where the Excel SUBTOTAL function is used. 

In this article, I will show you what a SUBTOTAL function is, and how to use the SUBTOTAL function in Excel in two methods with examples.

You’ll Learn:

• What Is SUBTOTAL Function in Excel?
• Using the SUBTOTAL Function From the Main Menu
• Using the SUBTOTAL Formula
  • Additional Case While Using the SUBTOTAL Formula

Watch our video on how to calculate subtotals in Excel

Related Reads:

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Calculate Factorial in Excel? Along with 2 Easy Examples

How to Merge Cells in Excel? 3 Easy Ways

What Is The SUBTOTAL Function in Excel?

The SUBTOTAL function helps in grouping and organizing the contents and helps in performing operations based on the user preferences. One advantage of using this function is that the SUBTOTAL houses various operations like sum, product, average, variance, etc, which makes it easier to calculate. 

In addition to that, using the SUBTOTAL function in Excel allows you to perform operations separately on visible and hidden values.  

Using the SUBTOTAL Function From the Main Menu

This is a relatively easy method to use the SUBTOTAL function. Consider an example. The table consists of units of sales made by the company in one month categorized by region and commodity type.

Note: SUBTOTAL only works if the separation data in the table are grouped. For example, if you want to calculate the SUBTOTAL based on the branch, then the sales and commodities of the particular branch should be grouped.

Since we are calculating the SUBTOTAL based on the branch, we need to group the branch category. To do this we can use Sort.

Select the cells of the table. Right-click and select Sort. You can select ascending (A to Z) or descending (Z to A) depending on your preference. 

This sorts the table based on the branch. Since we selected the A to Z sort, the table is arranged in terms of East, North, South, and West.

Once the change values are grouped together, we can now calculate the SUBTOTAL.

Navigate to Data. Under Outline, click on SUBTOTAL.

This opens up a new SUBTOTAL dialog box. 

In the At each change in: dropdown, select the option which you have grouped together or categorized the rows. This is where the SUBTOTAL function performs on similar data. 

In this case, since we are calculating the SUBTOTAL based on different branches, we can select Branch. In case you want to calculate the SUBTOTAL based on the commodity type, you can select the commodity type from the dropdown.

The second option is the Use function:. We know that the SUBTOTAL function houses a variety of operations like sum, count, average, product, etc. You can choose your preferred operation from the dropdown.

Finally, in the Add subtotal to: dropdown, select the column you want the function to act on. In this case, we have selected the Sales Made because we want the total of sales made in each branch. 

If you want to calculate the average of any other data, you can select the option and the function will operate on it.

Now, click Okay.

This gives you the SUBTOTAL and the Grand Total for the selected value. From the table, you can see the SUBTOTAL computes the value depending on the operation based on the criteria. The Grand total is the total of all the values. In other words, Grand Total calculates the total of all SUBTOTALs. 

While projecting data, you might not want an extensive report containing all the elements. In such cases, you can use the 1,2,3 buttons or (+) (-) symbols to show or hide the data. 

For example, if you only want to project the Total Sales based on the branch, you can click on the (2) button. This hides the extensive details like the Commodity Type and the Sales Made and shows only the sales made based on the branch. You can also use the (+) or (-) symbols to hide or show data.

If you want only the total Sales Made in a month irrespective of the branch or any other criteria, just click on the (1) button. This hides the branch details and projects only the Grand Total.

Note: If you add any filters, the SUBTOTAL function only works on the filtered values. For the SUBTOTAL to operate on the values, clear all filters and the function adds and calculates the values.

Suggested Reads:

3 Best Methods to Find Duplicates in Excel

How to Use Excel Split Screen? 3 Simple Ways

How to Copy Only Visible Cells in Excel? 3 Easy Ways

Using the SUBTOTAL Formula

Another way to arrive at the SUBTOTAL in Excel is by using a formula. 

Consider an example where you have the data for the sales made on individual lifestyle products.

Note: The table containing the data should always be PivotTable or normal data entry. The SUBTOTAL function does not work on Excel tables. Learn how to create a PivotTable from our previous guide.

If you want to calculate the SUBTOTAL, enter the formula =SUBTOTAL (function_num, ref1, [ref2], …) where function_num is the operation you want to perform on the data, and ref1 through ref2 is the range of the data.

I will explain the formula using another example. Consider a table consisting of the Product, Units Sold, and the Total Price of the commodities of a lifestyle brand over a period of one month.

To calculate the SUBTOTAL, enter the formula in the destination cell.

Once you enter the =SUBTOTAL(, Excel automatically shows you suggestions for the operations that you want to perform on the data.

In this case, we want to calculate the sum of the Total Price. So from the dropdown, double-click on 9, or select Sum and press the Tab key. This adds the function argument you want to perform on the data.

Now, for the range argument. You can either enter the range or select the range and it will get populated. In this case, we want to perform the sum operation on the Total Price and have entered the range C4:C8.  

Press Enter.

This gives you the SUBTOTAL of the Total Price. 

Additional Case While Using the SUBTOTAL Formula

While entering the operating parameter for the SUBTOTAL formula, you might wonder why there are two sets of operations with the same name.

Using the formula, we can perform several operations like calculating the average of the values, counting the number of entries, and so on.

The first set of operations (1-11) is used to calculate the values present in the table even if they are visible or hidden. The second set of operations (101-111) is used to calculate only visible values. They ignore the values that have been hidden manually.

Note: The above method “Using SUBTOTAL Function from Main Menu” only takes the first set of operations. The SUBTOTAL function in the Data option only includes hidden values when performing operations. 

From the above example, the SUBTOTAL sum of the Total Price of products is 271,000. Imagine if you don’t want to calculate the Total Price of the sock and you don’t want to display the row. You can hide it by selecting the row, then right-clicking it, and selecting Hide. In this case, I have hidden row 8.

Since we used the first set of operations from the above example, we can see the total price is the same even though we have hidden the sock row. 

Now, let us enter the second set of operations to calculate the SUBTOTAL. To calculate the sum of the products, we have passed the argument and operation number as 109. This ignores the hidden cell and gives the sum value of the visible cells.

In case the row is unhidden, the formula automatically takes the value into account and adds it to the SUBTOTAL.

Also Read:

How to Count Cells with Text in Excel? 3 Different Use Cases

How to Hide Formulas in Excel? 2 Different Approaches

How to Select Non Adjacent Cells in Excel? 5 Simple Ways

Frequently Asked Questions

Closing Thoughts

In this article, we saw what SUBTOTAL is, and how to use the SUBTOTAL function to calculate the values using 2 easy methods.

Using the SUBTOTAL helps to easily calculate multiple values, perform different operations on large tables, and help present them in an easy aesthetic way.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.