This article will focus on the **AGGREGATE vs SUBTOTAL** functions of Microsoft Excel. The **AGGREGATE** function in Excel contains nineteen distinct functions for achieving the desired results. Whereas in Microsoft Excel, **SUBTOTAL** is one of the strong functions that comes with nine different functions. These functions allow you to do a variety of Excel function operations.

**Syntax of AGGREGATE Function:**

**Reference form: AGGREGATE(function_num, options, ref1, [ref2], …)**

**Array form: AGGREGATE(function_num, options, array, [k])**

**Syntax of SUBTOTAL Function:**

**SUBTOTAL(function_num,ref1,[ref2],…)**

With examples and adequate representations, we will explain the distinctions between the **AGGREGATE** and **SUBTOTAL** functions in Excel in this post.

## Download Practice Workbook

You can download the practice workbook from the following download button.

## 4 Differences Between AGGREGATE vs SUBTOTAL Functions in Excel

While using the **AGGREGATE vs SUBTOTAL** functions in Excel, we noticed some differences. Here are they-

### 1. Unlike AGGREGATE, (Available Since 2010), SUBTOTAL Function Is Available in Excel Since 2007

**AGGREGATE Function**:

The** AGGREGATE** function is compatible with **Excel for Microsoft 365**, **Excel for Microsoft 365 for Mac**, **Excel for the web**, **Excel 2021**, **Excel 2021 for Mac**,** Excel 2019**, **Excel 2019 for Mac**, **Excel 2016**, **Excel 2016 for Mac**, **Excel 2013**, **Excel Web App**, **Excel 2010**, **Excel for Mac 2011**, **Excel Starter 2010**.

**SUBTOTAL Function**:

The **SUBTOTAL** function is accessible in **Microsoft Excel 365**, **Microsoft Excel 365 for Mac**, **Excel for the web Excel 2021**, **Excel 2021 for Mac**, **Excel 2019**, **Excel 2019 for Mac**, **Excel 2016**, **Excel 2016 for Mac**, **Excel 2013**, **Excel 2010**, **Excel 2007**, **Excel for Mac 2011**, **Excel Starter 2010**.

**Read More: ****How to Use Excel AGGREGATE Function with Multiple Criteria**

### 2. SUBTOTAL Function Let You Specify Whether to Include or Exclude Hidden or Filtered Rows

The **AGGREGATE function **is preferable to **SUBTOTAL **since it has more functions and features. **SUBTOTAL****, **on the other hand, allows us to decide whether to include or omit manually hidden rows. However, both functions can exclude manually concealed and filtered-out rows from the assessment.

#### i. Dealing with Hidden Rows

For data manipulation, sometimes we have to hide or unhide rows. Now we will see the effect of hiding on the **AGGREGATE** and **SUBTOTAL** functions.

**📌**** Steps:**

- First and foremost, here is a data collection including two groups of students and their class
**Marks**on a certain subject. The**Group 1**and**Group 2**cells in**B8**and**B12**are the total marks of the corresponding groups.

- With the
**AGGREGATE**function, you may conduct 19 different functions. Individual integers define each function. In addition, the**SUBTOTAL**function provides 11 Excel functions that may be performed by inputting the relevant parameter. - MS Excel includes a
**SUM**function that returns the**SUM**of numbers, however when there is a**SUM**Function in the range, such as the cells in**C8**and**C12**, the overall summation of the outcome might be deceptive. To solve this problem, Excel has**AGGREGATE**and**SUBTOTAL**functions to find the sum and many other functions to ignore the sub summation in the range like in the image below. - In cell C14, the following formula is used to find the sum of 2 groups:

`=SUBTOTAL(9,C5:C12)`

**SUBTOTAL(function_num,ref1,[ref2],…)****function_num= 9 (SUM)****ref1= C5:C12**

- The following formula is used to find the sum of 2 groups in cell
**C15**:

`=SUBTOTAL(109,C5:C12)`

Here,

**SUBTOTAL(function_num,ref1,[ref2],…)****function_num= 109(SUM)****ref1= C5:C12**

- Again, the following formula is used to find the sum of 2 groups in cell
**C16**:

`=AGGREGATE(9,3,C5:C12)`

**AGGREGATE(function_num, options, ref1, ref2, …)****function_num= 9(SUM)****options= 3****ref1= C5:C12**

- Now we will hide a row to see the effect on the functions and their corresponding different functionality.
- Select
**Row 9**and**right click**on the mouse and then select**Hide**.

- This number in the
**SUBTOTAL**function, which can range from 1 to 11 or 101 to 111, indicates which function will be used for the**SUBTOTAL**. 1-11 functions in**SUBTOTAL**purposefully ignore concealed rows, however, 101-111 do not; filtered-out cells are always omitted. **options 3**in**AGGREGATE**purposefully ignore concealed rows.- For that reason,
**C14**is unchanged with hidden rows. However,**C15**and**C16**have been changed.

#### ii. Dealing with Filtered Data

Filtering rows is sometimes required for data modification. Now we’ll look at how filtering affects the **AGGREGATE** and **SUBTOTAL** functions.

**📌**** Steps:**

- Now we will add a
**Filter**to the data like the image below to observe the effect on those functions.

- After adding
**Filter**, we will click on the drop-down menu to filter out**Group 1**and**Milton**and press**OK.**

- Like in the image, filtering out data does not show any different effect on the two functions.
**SUBTOTAL**and**AGGREGATE**functions both are sensitive to filter out data.

**Read More: ****How to Aggregate Data in Excel (3 Easy Ways)**

### 3. AGGREGATE Function Can Deal with Input Range That Has Erroneous Values in It

Cells containing invalid data like **#DIV/0!** are a major obstacle to data modification. We’ll look at how invalid data affects the **AGGREGATE** and **SUBTOTAL** functions.

**📌**** Steps:**

- Now the ultimate difference between
**SUBTOTAL**and**AGGREGATE**functions is that**AGGREGATE**can ignore error values whether**SUBTOTAL**can’t.

**Read More: ****How to Use Conditional AGGREGATE Function in Excel (2 Methods)**

### 4. AGGREGATE Offers 10 More Functions Compared to SUBTOTAL Function

**SUBTOTAL** has 9 different functions inside it while **AGGREGATE** has 10 more functions which make it more diverse than **SUBTOTAL**.

#### i. VAR.S (Function Number 10) and VAR.P (Function Number 11)

You must utilize the **VAR.S function**, which is function number **10**, to compute the **VARIANCE** of a sample dataset with the **AGGREGATE** function. To determine the **VARIANCE** of an entire population, utilize Excel’s **VAR.P** function, which is function number 11.

#### ii. MEDIAN (Function Number 12)

In Excel, the **MEDIAN function** returns the data set’s middle number.

We obtained the expected output, **83.5**, in our result cell after applying the **MEDIAN **function with the aid of the **AGGREGATE** function.

#### iii. MODE.SNGL (Funciton Number 13)

The **MODE.SNGL** function returns the value that occurs the most frequently inside a given range. In Excel, this is also a statistical function.

Take the following example, in which 87 appears twice while the remaining digits appear just once.

#### iv. LARGE (Function Number 14)

The **LARGE function** returns the largest number in a given sample. It has function number 14, which indicates that when we run this function with the **AGGREGATE**, we must include the [k] as the fourth argument.

- Now we will enter the following formula in
**C12**:

`=AGGREGATE(14,4,C5:C10,2)`

**14 = **function number, means the LARGE function

**4 **= option, which means we will ignore nothing

**C5:C10** = cell references that have the values to extract the result

**2 **= 2nd largest value (if you want to get the largest value within a dataset then write 1, if you want to get the 3rd largest value then write 3, and so on)

- In our dataset, the highest value is 98. However, because we used 2 in the k-th option, we intended to have the 2nd greatest number in our dataset. Because 87 is the second largest, we received 87 as our output.

#### v. SMALL (Function Number 15)

The **SMALL function** in Excel delivers the smallest integer in each dataset. As previously explained, while running this function with the **AGGREGATE**, we must put the [k] as the fourth parameter.

- At this moment, in
**C12,**we will enter the following formula:

`=AGGREGATE(15,4,C5:C10,2)`

**15**= function number means the **SMALL** function

**4 **= option, which means we will ignore nothing

**C5:C10** = cell references that have the values to extract the result

**2 **= 2nd smallest value (if you want to get the smallest value within a dataset then write 1, if you want to get the 3rd smallest value then write 3, and so on)

- In our dataset, the smallest value is 50. However, because we used 2 in the k-th option, we intended to have the 2nd smallest number in our dataset. Because 65 is the second smallest, we received 65 as our output.

#### vi. PERCENTILE.INC (Function Number 16) and PERCENTILE.EXC (Function Number 18)

Excel’s **PERCENTILE function** computes the k-th percentile given a piece of data. A percentile is a number that represents the percentage of values in a data collection that fall below that value. The value of k might be either decimal or a percentage. In other words, the number for the tenth percentile should be entered as 0.1 or 10%. A percentile computed with 0.2 as k, for example, implies that 20% of values are less than or equal to the calculated result, but a percentile calculated with 0.5 as k means that 50% of values are less than or equal to the calculated result.

- Again, in
**C12,**we will enter the following formula:

`=AGGREGATE(16,4,C5:C10,0.3)`

`=AGGREGATE(18,4,C5:C10,0.4)`

**16**= function number means the PERCENTILE.INC function

**18= **function number means the PERCENTILE.EXC function

**4 **= option, which means we will ignore nothing

**C5:C10** = cell references that have the values to extract the result

**0.3**= 30% of values are less than or equal to the calculated result

#### vii. QUARTILE.INC (Function Number 17) and QUARTILE.EXC (Function Number 19)

The **QUARTILE function** in Excel returns the quarter part (four equal groups) of a total set of data.

- Now to calculate
**QUARTILE**, we will enter the following formula:

`=AGGREGATE(17,4,C5:C10,2)`

`=AGGREGATE(19,4,C5:C10,1)`

**17**= function number means the QUARTILE.INC function

**19**= function number means the QUARTILE.EXC function

**4 **= option, which means we will ignore nothing

**C5:C10** = cell references that have the values to extract the result

**2**= Second quartile, 50th percentile

**1=** first quartile, 25th percentile

**Read More: ****Combining AGGREGATE with IF Function in Excel (4 Examples)**

## Conclusion

To discern between **AGGREGATE** vs **SUBTOTAL** in Excel, follow these procedures and stages. You are invited to download and use the workbook for your own practice. Please post any questions, problems, or recommendations in the comments box of our blog **ExcelDemy**.