Excel SUBTOTAL Function in Office 365!! (*With Examples*)
Tags: excelfunctions, excelsubtotalfunctio
Published: 2020-08-27 11:02:39

---
title: Excel SUBTOTAL Function in Office 365!! (*With Examples*)
published: true
date: 2020-08-27 09:22:28 UTC
tags: ExcelFunctions,ExcelSUBTOTALFunctio
canonical_url: https://geekexcel.com/excel-subtotal-function-in-office-365-with-examples/
---

## Excel SUBTOTAL Function:

This article describes the explanation, basic syntax, and usage of the **Excel SUBTOTAL function** in **Office 365**. Get an official version of **Microsoft Excel** from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel).**
## SUBTOTAL Function – Description

- The **SUBTOTAL function**  **returns an aggregate result in a list or database**.
- Using this function, you can summarize amounts by **including or exclude hidden/filtered rows**.

## Syntax of SUBTOTAL Function:

```
=SUBTOTAL(function\_num, ref1, [ref2],...)
```

### **Argument Description:**

- **Function\_num (Required)** – Function that needs to use for the subtotal.
  - Numbers from **1 to 11** – Includes manually hidden rows.
  - Numbers from **101 to 111** – Excludes manually hidden rows & filtered-out cells.

| **Function\_num (includes hidden values)** | **Function\_num (Excludes hidden values)** | **Function** |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |

- **Ref1 (Required)** – First named reference or range to find the subtotal.
- **Ref2,… (Optional)** – Named references or ranges 2 to 254 that you wish to find the subtotal.

### Important Note:

- The SUBTOTAL function is **only for vertical ranges (columns of data)** and not for horizontal ranges (rows of data).
- This function excludes any rows that are not included in the filter result.
- Nested subtotals are neglected to avoid double counting.
- **#VALUE! Error** – If any of the references or ranges are 3-D references, the function returns the #VALUE! Error. For example, referring the same cells from Sheet 1 & Sheet 2,  =SUBTOTAL(E1:E5, E1:E5) will return an error.

## Practical Examples:

Let’s look at some examples of this **SUBTOTAL function** and explore how it works in Microsoft Excel.

**Example 1: SUM using SUBTOTAL (Including & Excluding Manually Hidden Rows)**

- [**SUM Function**](https://geekexcel.com/sum-function/) calculates the total sales.

![SUM Function](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/SUM-Function.png)<figcaption id="caption-attachment-12751">SUM Function</figcaption>

- After applying a filter, two rows are hidden manually, the SUM Function includes the hidden row values.

![SUM Function includes hidden row values](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/SUM-Function-includes-hidden-row-values.png)<figcaption id="caption-attachment-12750">SUM Function includes hidden row values</figcaption>

- Now, using the **SUBTOTAL Function** , we can calculate the **SUM** by **excluding manually hidden rows**. Here, the **argument 109** is used for calculating SUM by excluding hidden rows.

![SUBTOTAL Function - Excludes manually hidden row values](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/SUBTOTAL-Function-Excludes-manually-hidden-row-values.png)<figcaption id="caption-attachment-12747">SUBTOTAL Function – Excludes manually hidden row values</figcaption>

- The SUBTOTAL function can calculate the **SUM** by **including manually hidden rows** , so we have used the **argument 9** for calculating SUM by including hidden rows.

![SUBTOTAL Function - Includes manually hidden row values](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/SUBTOTAL-Function-Includes-manually-hidden-row-values.png)<figcaption id="caption-attachment-12748">SUBTOTAL Function – Includes manually hidden row values</figcaption>

**Example 2: AVERAGE using SUBTOTAL**

- Using the SUBTOTAL Function, we have calculated the **AVERAGE** of Total Cost.

![SUBTOTAL Function to calculate AVERAGE](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/SUBTOTAL-Function-to-calculate-AVERAGE.png)<figcaption id="caption-attachment-12753">SUBTOTAL Function to calculate AVERAGE</figcaption>

## Verdict:

In the above short article, we have described the explanation, basic syntax, and usage of the **Excel SUBTOTAL function** in **Office 365** with the examples. Drop your valuable **queries/feedback** in the below comment section. Thanks for visiting **[Geek Excel!!](https://geekexcel.com/)** Keep Learning with us!!

## Related Articles:

- [How to Use Excel NA Function in Office 365? – {with Examples}](https://geekexcel.com/how-to-use-excel-na-function-in-office-365-with-examples/)
- [Excel IFS Function in Office 365 – Explanation with Examples!!](https://geekexcel.com/ifs-function-in-ms-excel-365-explanation-with-examples/)
- [Use Excel ISFORMULA Function in Office 365 -Simple methods!!](https://geekexcel.com/use-isformula-function-in-microsoft-excel-365-simple-methods/)
- [How to Use Excel BAHTTEXT Function in Office 365?](https://geekexcel.com/how-to-use-bahttext-function-in-microsoft-excel-365/)