Excel In Depth - AGGREGATE Function: Episode 1231

542 views · Published 26 August 2010 · 2:59 · Indexed 16 November 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel AGGREGATE Function: Exploring Chapter 11 and its Powerful Features | MrExcel Podcast.

Welcome back to the MrExcel podcast. In this episode, we will be diving into Chapter 11 and exploring the powerful AGGREGATE function. I'm your host, Bill Jelen, and I am excited to share with you the ins and outs of this game-changing function.

While Excel 2010 introduced many new functions, most of them were simply international issues for standardizing names. However, the AGGREGATE function stands out as a truly valuable addition. With this function, you can perform any of the 11 calculations available in the SUBTOTAL function, as well as some that were previously missing. This includes median, mode, large, small, percentile, and quartile calculations.

One of the key advantages of the AGGREGATE function is its ability to ignore certain rows or cells. Similar to the SUBTOTAL function, it can ignore other SUBTOTAL functions and hidden rows. However, it goes a step further by also being able to ignore error cells. This opens up a whole new realm of possibilities, as you can now use this function in array formulas.

To use the AGGREGATE function, you will need to specify two arguments. The first is the function number, which now includes 19 options instead of the previous 9 in the SUBTOTAL function. The second argument is a number that corresponds to which items you want to ignore. This can be determined by referencing a table provided in the video.

One of the most useful features of the AGGREGATE function is its ability to handle error cells. In situations where you need to add up numbers while excluding hidden rows, other subtotals, or error cells, this function is a game-changer. It allows you to get an accurate total without having to manually adjust for these factors.

Thank you for tuning in to this episode of the MrExcel podcast. I hope you found this deep dive into the AGGREGATE function helpful and informative. Be sure to join us next time for another netcast, where we will continue to explore the many features and functions of Excel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to MrExcel podcast sponsored by Easy-XL 
(00:12) Excel 2010's new function: Aggregate 
(00:26) How to use the Aggregate function 
(01:01) Three different classes of things that Aggregate can ignore 
(01:49) How to specify the range for Aggregate 
(02:02) Some limitations of Aggregate 
(02:49) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Aggregate function in Excel
Excel 2010 functions
Excel probability functions
Excel statistics functions
Ignore error values in Excel
Large function in Excel
Median function in Excel
Mode function in Excel
Percentile function in Excel
Quartile function in Excel
Small function in Excel
Subtotal function in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152912/


Excel 2010 adds a new function called 'AGGREGATE'.
This function is similar to SUBTOTAL, but with several new tricks, including MEDIAN, LARGE, SMALL, and the ability to ignore error values. Episode #1231 explores this new Excel Function.

More from this channel