Excel In Depth - AGGREGATE Function: Episode 1231
542 views · Published 26 August 2010 · 2:59 · Indexed 16 November 2026
Channel: MrExcel.com · 2010 · Education
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920