Here, we will learn how to use the Excel math functions: SUBTOTAL and AGGREGATE.
SUBTOTAL function
The Excel SUBTOTAL function allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, MAX, etc. The function can be found in all the versions of Excel. For example, in the screenshot shown, I filter the table to show sales only for the "North" region. The SUBTOTAL formula adjusts automatically to remove all other regions. Here, the SUBTOTAL 9 formula has been used which uses the SUM function to sum up the values. Similarly, SUBTOTAL 1 uses AVERAGE, SUBTOTAL 2 uses COUNT, SUBTOTAL 3 uses COUNTA, SUBTOTAL 4 uses MAX, SUBTOTAL 5 uses MIN, and so on.
AGGREGATE function
The Excel AGGREGATE function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring errors. The function can be found in Excel 365 - Excel 2010. For example, in the screenshot shown, the AGGREGATE 4 formula has been used which uses the MAX function to return the largest numeric value in the range E4:E14. To ignore values, the sixth option has been used that ignores error values.
You have successfully learnt how to use the Excel math functions: SUBTOTAL and AGGREGATE.
