Excel Rev Up - AGGREGATE Function: Episode 1307

559 views · Published 16 December 2010 · 2:09 · Indexed 30 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Exploring the AGGREGATE Function in Excel.

Welcome to another episode of the MrExcel podcast. In this episode, we will be diving into the world of Excel 2010 and exploring the AGGREGATE function in Chapter 12 of our Rev Up series. With over 400 functions in Excel 2010, the AGGREGATE function is one that you definitely don't want to miss.

If you're familiar with the SUM function, you may have encountered the issue of N/A values causing your total to also be N/A. This is where the AGGREGATE function comes in. Similar to the SUBTOTAL function, AGGREGATE offers 11 different calculations including Average, Count, Max, Min, Product, Standard Deviation, Sum, and Variance. But it also includes some new ones such as Median, Large, Small, and Percentile.

One of the great features of the AGGREGATE function is the ability to ignore hidden rows and error values. This is something that was not possible with the SUBTOTAL function. For example, if you have a range of data with some N/A values, you can use the AGGREGATE function to add up everything except for those N/A values. This is perfect for when you just want to get a quick overview of your data without having to go back and fix the errors.

But that's not all, the AGGREGATE function also allows you to perform other tasks such as ordering rows and finding the median. It's a versatile function that can be used in various scenarios and is definitely a must-have in your Excel toolkit. So if you're tired of dealing with N/A values and want to take your data analysis to the next level, be sure to check out the AGGREGATE function in Excel 2010.

Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time with another netcast from MrExcel.

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) Aggregates 
(00:20) New Function: Aggregate 
(00:36) Comparison to Subtotal 
(00:57) Additional Functions in Aggregate 
(01:07) Ignore Hidden Rows 
(01:17) Similarities to Subtotal 
(01:27) Ignore Error Values 
(01:37) Example of Aggregate Function 
(01:48) Other Uses for Aggregate 
(01:58) 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
Aggregates
Descriptive statistics
Ignore error values
Ignore hidden rows
N/A values
Probability functions
Statistical functions
Subtotal function


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


Excel 2010 now contains 400 Functions. Amongst those new Functions is a new Function called 'AGGREGATE'. AGGREGATE is similar to SUBTOTAL, but with several new tricks, including the ability to ignore Error Values. "Rev Up to Excel 2010" by Bill Jelen

More from this channel