Quick Links

• The SUBTOTAL Function

• The AGGREGATE Function

• Summary: SUBTOTAL vs. AGGREGATE in Excel

For many years, I used the SUBTOTAL function in Microsoft Excel to create easily visible subtotals at the top of my worksheets. However, when I encountered AGGREGATE, this became my go-to function for achieving the same result but with more flexibility.

Before I explain further, let's remind ourselves how the SUBTOTAL function works.

The SUBTOTAL Function

Microsoft Excel's SUBTOTAL function is quite simple to use. It lets you perform subtotal calculations for ranges of cells, and you can decide whether you want to include manually hidden rows in the result.

Related

How to Use the SUBTOTAL Function in Microsoft Excel

See the subtotal of values with this simple Excel function.

Posts

However, rows that are filtered out are always excluded, and the SUBTOTAL function doesn't work if there are errors in the data, unless you nest it within the IFERROR function. What's more, SUBTOTAL only supports 11 functions, limiting its use if you want to perform more complex statistical calculations.

Here's the syntax:

=SUBTOTAL(<em>a</em>,<em>b</em>,<em>c</em>)

where

• a (required) is a number that represents the function you want to use in the calculation,
• b (required) is the first range of cells to subtotal, and
• c (optional) represents the first of up to 252 more ranges to subtotal, each separated by a comma.

For argument a, 1 or 101 = AVERAGE, 2 or 102 = COUNT, 3 or 103 = COUNTA, 4 or 104 = MAX, 5 or 105 = MIN, 6 or 106 = PRODUCT, 7 or 107 = STDEV, 8 or 108 = STDEV.P, 9 or 109 = SUM, 10 or 110 = VAR, and 11 or 111 = VARP.

How to Sort and Filter Data in Excel

Microsoft Excel has power sorting and filtering options. Here's how to use them in your spreadsheet.

Posts

In this final example, you can see that the SUBTOTAL function returns an error if there's an error in the range.

To avoid these issues, you can use the AGGREGATE function instead.

The AGGREGATE Function

The AGGREGATE function is similar to the SUBTOTAL function, but there are more options for the type of aggregation you want to create. Also, you have a wider choice of things you can exclude from the result, and you can make Excel ignore errors in your data.

In other words, compared to the SUBTOTAL function, AGGREGATE is more powerful and flexible.

The AGGREGATE function has two syntaxes—one for references and one for arrays—though you don't need to worry about which one you're using, as Excel selects the relevant one depending on the arguments you input.

Related

How to Use Excel's AGGREGATE Function to Refine Calculations

Miss out hidden rows, errors, or nested functions in your formulas.

Posts

The syntax for the reference form of the AGGREGATE function is:

=AGGREGATE(<em>a</em>,<em>b</em>,<em>c</em>,<em>d</em>)

where

• a (required) is a number that represents the function you want to use in the calculation,
• b (required) is a number that defines what you want the calculation to ignore,
• c (required) is the range of cells on which the function will be applied, and
• d (optional) is the first of up to 252 additional arguments that specify further ranges.

If you're working with arrays, the AGGREGATE function follows a slightly different syntax:

=AGGREGATE(<em>a</em>,<em>b</em>,<em>c</em>,<em>d</em>)

where

• a (required) is a number that represents the function you want to use in the calculation,
• b (required) is a number that defines what you want the calculation to ignore,
• c (required) is the array of values on which the function will be applied, and
• d is the second argument required by array functions like LARGE, SMALL, PERCENTILE.INC, and others.

The first difference to note between SUBTOTAL and AGGREGATE is that the latter has more options for argument a: 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN, 6 = PRODUCT, 7 = STDEV.S, 8 = STDEV.P, 9 = SUM, 10 = VAR.S, 11 = VAR.P, 12 = MEDIAN, 13 = MODE.SNGL, 14 = LARGE, 15 = SMALL, 16 = PERCENTILE.INC, 17 = QUARTILE.INC, 18 = PERCENTILE.EXC, and 19 = QUARTILE.EXC.

However, where AGGREGATE really trumps SUBTOTAL is in its ability to exclude certain values from the result (argument b):

Number	What Is Ignored
0	Nested SUBTOTAL and AGGREGATE functions
1	Hidden rows, and nested SUBTOTAL and AGGREGATE functions
2	Errors, and nested SUBTOTAL and AGGREGATE functions
3	Hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
4	Nothing
5	Hidden rows only
6	Errors only
7	Hidden rows and errors

So, let's see it in action.

In this example, typing:

=AGGREGATE(1,7,Team_Scores[Score])

into cell E2 returns the average (argument a = 1) of the visible values in column B, ignoring the hidden value in row 8 and the error in row 7 (argument b = 7).

Related

How to Hide Cells, Rows, and Columns in Excel

There may be times when you want to hide information in certain cells or hide entire rows or columns in an Excel worksheet.

Posts

You wouldn't be able to get the same result using the SUBTOTAL function alone in this scenario, as it can't overlook errors without being nested within an IFERROR formula.

Summary: SUBTOTAL vs. AGGREGATE in Excel

Here's a summary of Microsoft Excel's SUBTOTAL and AGGREGATE functions:

Feature	SUBTOTAL	AGGREGATE
Suitable for straightforward aggregations	Y	Y
Suitable for complex statistical aggregations	N	Y
Number of functions supported	11	19
Can include or exclude manually hidden rows	Y	Y
Can include or exclude filtered rows	N	Y
Can ignore errors	N	Y
Can ignore nested SUBTOTAL or AGGREGATE results	Y	Y
Offers further options for array functions	N	Y

---

Even if you think you've settled on a function that works for you, always double-check that there isn't another function that does the job even better! Microsoft often introduces new functions that are upgrades from older ones—for example, I now use XLOOKUP instead of VLOOKUP, and XMATCH is much more user-friendly than MATCH.

以上がMicrosoft Excelのサブテルと集合體の詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國(guó)語(yǔ) Web サイトの他の関連記事を參照してください。