A subtotal is the aggregation of a data set, typically showing the totals of a certain section in that data set. A budgeting spreadsheet is a good example for this where subtotals are used to display more details of certain categories. In a budgeting application, you may want to categorize the expenses, and break them down like household, transportation, and social activities. In this guide, we’re going to show you how to calculate subtotal in Excel.

## Using generic functions

The subtotal calculations are basic aggregation operations. You can easily get the subtotals using functions like **SUM**, **AVERAGE** or **COUNT**. On the other hand, using these formulas in the middle of tables can sometimes cause issues. Especially, when you need to get total of every section. In this case, you either need to use other formulas to ignore those subtotal values, or set ranges individually for each formula to avoid calculating those values multiple times.

In the example below, the **SUM **function is used to calculate the total of each *Home Expenses* and *Transportation,* as well as a grand total. Although the total expense value should be *655 (455 + 200)*, the formula in cell *F28* shows *1,310*. The reason is that the **SUM** function calculates the subtotal values with the actual data.

This can be resolved by excluding the subtotal cells from the function. or subtracting them from the result. However, this obviously means additional work initially, and when you need to update your table. Excel’s solution to these types of scenarios is the **SUBTOTAL** Function.

## Using the SUBTOTAL Function to calculate subtotals in Excel

The **SUBTOTAL** function is like the *Swiss Army Knife* of Excel functions. The function combines several capabilities in a single function, and also has two additional features:

- Ability to ignore other
**SUBTOTAL**functions in the range - Exclude filtered and hidden rows from the calculation

These two features make the **SUBTOTAL** function a perfect tool to calculate subtotals in Excel (obviously!). Let’s see how you can use the function.

The **SUBTOTAL **function can replicate what other functions does. Supported functions include:

1. AVERAGE | 2. COUNT | 3. COUNTA |

4. MAX | 5. MIN | 6. PRODUCT |

7. STDEV | 8. STDEVP | 9. SUM |

10. VAR | 11. VARP |

To define which function you want to replicate, you need to enter its defined code as an argument. After this, enter the other arguments as if you were using that function. For example, use *9* for a sum operation.

*means*=SUM(F7:F14)

If the aggregation range doesn’t contain any other **SUBTOTAL** functions, both formulas return the same result.

### Calculating the subtotal in Excel

Where the **SUBTOTAL** function shines is when you need to calculate all values. If you are using the **SUBTOTAL**, you do not need to worry about duplicate calculations. The function ignores its counterparts in the selected range. Thus, you will get the sum of all data without issues.

The example below shows how the **SUBTOTAL** and the **SUM** functions work in unison. While the **SUBTOTAL** returns the correct value (*655*), **SUM** returns double that value (*1310*).

### Ignoring filtered and hidden rows

Another benefit of the **SUBTOTAL** function is its ability to ignore hidden rows and columns when aggregating the data. By default, the function ignores all values that are filtered out . On the other hand, you need to change the code of operations to enable this.

Instead of using codes from 1 to 11, use the numbers between 101 and 111 in the respective order. For example, *101* for **COUNT** instead of *1*, *102* instead of *2*, and so on.

You can see the difference in the example below. The formulas on the left side do not ignore the hidden rows between 9 and 11. These 3 rows have total of *110*. As a result, while the left side shows *455* as total value, the formula on the right side shows *345*. The difference is *110*.

For more information and examples, please see: **SUBTOTAL**.