This 1 Excel function can do 90% of your calculations!

⬇️ Download free Excel example file: bit.ly/fn-agg1
The AGGREGATE function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions in one fell swoop:
AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC
But that’s not reason enough to use it, the function-killer reason to use AGGREGATE over any of the 19 standard functions it can replace is because it is far more powerful. Let me show you.
LEARN MORE
===========
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join 320K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 The Swiss Army Knife of functions
0:19 The problem with other functions
1:00 Array or Reference Form
1:54 Handling arrays
2:59 Exploiting errors
4:38 Caution
5:03 Limitations
#excelfunctions #excelformula #exceltutorial

[@a.achirou6547]

Excellent job Mynda. This is really the Swiss Knife of functions. This is amazing and thank you for bringing it up back to our attention.

[@MyOnlineTrainingHub]

So glad you agree 🙏😊

[@DJPGB]

This is what I like! I was aware of the AGGREGATE function, but this is more than an excellent introduction. It's a great review and reminder of the features and gotchas of the function. Thanks!

[@MyOnlineTrainingHub]

Thanks for your kind words 🙏😊

[@SergioAlejandroCampos]

That’s great Mynda.

[@MyOnlineTrainingHub]

Thanks so much, Sergio! 😊

[@chrism9037]

This is awesome Mynda. This is one of those functions I know is out there but rarely use, but you've shown me some great ways to use it, thanks!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Chris! Thanks for watching 😊

[@kevinmahernz]

Same here, was aware of it but hadn't bothered to look deeply at it. Thank you.

[@dougmphilly]

the hidden gem of excel functions. i use it all of the time.

[@MyOnlineTrainingHub]

Awesome to hear 👍

[@Renato38640]

Mynda, you are so great! Thanks for this lesson!

[@MyOnlineTrainingHub]

Thank you so much! 😃

[@kebincui]

Excellent video as always. Thanks Mynda

[@MyOnlineTrainingHub]

Glad you enjoyed it! Thanks for watching 🙏😊

Wonderful! Very powerful! Thanks a lot!

[@MyOnlineTrainingHub]

Glad you liked it! 🙏😊

[@ernestochavez9560]

Thank you so much. Need to spend some time with them for familiarise with the kids. Loved you image in a bubble too, thus you keep us company all along.

[@MyOnlineTrainingHub]

Great to hear! 🙏😊

[@RogerStocker]

Nice feature, well explained thanks Mynda!

[@MyOnlineTrainingHub]

Thanks so much, Roger 🙏

[@jerrymiles7804]

Outstanding! dear Mynda Treacy - You are number ONE! Very powerful function! Best regards from Limón, Costa Rica!

[@MyOnlineTrainingHub]

🥰 thanks so much 🙏 glad you liked it!

[@ziggle314]

First rate. I was never clear on the k variable listed for aggregate -- now I am. Also like how you returned multiple answers with an array constant. Thanks!

[@MyOnlineTrainingHub]

Awesome to hear 🙏😊

[@MrSith-yp3yq]

You are the best Excel guru out there. Love your videos 🙂

[@MyOnlineTrainingHub]

Wow, thank you! 🥰

[@wills_gotsneakers3954]

This is money! Fantastic explanation and example

[@MyOnlineTrainingHub]

So pleased you enjoyed it 🙏😊

[@johncowie3092]

Very very useful I learned something here

[@MyOnlineTrainingHub]

Awesome to hear! 😊

[@eryabolonha]

Oh wow, thank you!

[@MyOnlineTrainingHub]

You're welcome! 😊

[@videosGNP]

Excellent video!

[@MyOnlineTrainingHub]

Thank you very much!

[@ahmadalali96]

Many thanks 🎀 🎀 🎀

[@MyOnlineTrainingHub]

You are so welcome! Thanks for watching.

[@marcusfranconium3392]

Verry good video , also verry to extremly usefull . it will safe me tons of time .

[@MyOnlineTrainingHub]

Thanks so much. So pleased to hear that 😊

[@kylelane630]

Hi Mynda, this is a fantastic video. I have just been reading through the email that supports this. It's great also to see good use (I am guessing) of the PowerPoint Cameo tool. 👍🏽👏🏽👏🏽👏🏽👏🏽

[@MyOnlineTrainingHub]

So glad to hear 🙏 haven't used PowerPoint cameo tool though...Googling that now 😁

[@joshuamanampiu6489]

Awesome!

[@MyOnlineTrainingHub]

Glad you think so!

[@bennicholls8384]

brilliant!

[@MyOnlineTrainingHub]

Thank you!

[@quentinbricard]

Thank you for this video! ... 🤔 ... 😳 ... WOW!🤩🤩🤩

[@MyOnlineTrainingHub]

😁thanks for watching!

[@IamTheReaper911]

Once again 💅🔨 (nailed it) ❤😎

[@MyOnlineTrainingHub]

🥰🙏

[@alexeyzvontsov511]

thank you very much! how could I not know about this?!)😀

[@MyOnlineTrainingHub]

Better late than never 😁

[@steven.h0629]

Cheers! 🥂

[@MyOnlineTrainingHub]

Thanks for watching, Steven 😊🙏

[@bhaveshshah4477]

Great

[@MyOnlineTrainingHub]

Glad you liked it!

[@olivierissaverdens6916]

Mynda, thank you once more for this very useful video! Could you please post the link to the video you mentionned on how to use the aggregate function with slicers and tables?

[@MyOnlineTrainingHub]

oops, this video was supposed to be for next week 🤦‍♀️ here's the other video: kzbin.info/www/bejne/oGbLgmaNdt1pqas

[@olivierissaverdens6916]

@@MyOnlineTrainingHub Thank you very much Mynda!

[@keylanoslokj1806]

Great video Mynda. I have two questions. 1) does Aggregate exist in version 2016? And 2) can we include formulas in our VBA code? (Or at least reference them...).

[@MyOnlineTrainingHub]

Thank you! Yes, it's available in all versions from 2010 onward. AGGREGATE works just like any other function in regards to VBA.

[@taranawa]

Hi Mynda, can you also use what you have described here in a Pivot table? I don't know how to reference the pivot table column name in my formula.

[@MyOnlineTrainingHub]

To reference a PivotTable, you should use the GETPIVOTDATA function explained here: kzbin.info/www/bejne/gnXJkIKIo7h2q5Y

[@MMSummer26]

Hi Mynda, I learn a lot with each of your videos! Many thanks! I have one question regarding the "calculation" for the result in 4:32: The results from the >average function (without errors) are divided by the array of the viewers. How is this working in the background? I don't get it. Not sure if my question is clear. So I'll try this way, too to hopefully can transfer what I mean: If I divide an Array through a subset of the same array: What is there calculated in the background? Many thanks for any help. 🙂

[@MyOnlineTrainingHub]

Each of the 12 values in the Viewers column is divided by the corresponding value returned by the denominator array. e.g. 91/0, 87/0, 99/0, 102/0, 125/0, 140/1....

[@MMSummer26]

@@MyOnlineTrainingHub Ah, all right. Now I know where I made my mistake. The logical test does not return the value for a match, but the 1, which can then be evaluated using the aggregate function. Many thanks!!! 👍♥

[@shoppersdream]

Very Nice, Mynda! Thanks! If you get a Pivot Table with Aggregate Value and Aggregate Value2 in Column Bucket of Pivot Table Field List. How should you check the Details of Aggregate function in those Fields? Thanks

[@MyOnlineTrainingHub]

Thanks! The PivotTable can't tell you the underlying formula in the source data for any column and AGGREGATE used here is no different. You'd be best to avoid making the formulas for PivotTable source data changeable for this reason.

[@shoppersdream]

@@MyOnlineTrainingHub Thanks! The problem is that there are already a lot of formulas in the Data Source. Maybe they were used as Helper Columns. Thanks

[@konanki]

Awesome functions. My only worry is how to get to this video when I need to use those functions. Can I tag this video in KZbin with specific tags so, when I search with those tags, I get to this video?

[@MyOnlineTrainingHub]

You can bookmark it and save it to a playlist for future reference.

[@Quidisi]

Wow! at 5:20 So AGGREGATE does not ignore manually hidden rows, but SUBTOTAL will. That is good to know! I had wondered if SUBTOTAL had been rendered useless, but there is at least one use-case in its favor. Anybody aware of any other SUBTOTAL use cases that AGGREGATE doesn't do?

[@MyOnlineTrainingHub]

Glad it was worth your time to watch 😊🙏 AGGREGATE does ignore both filtered and manually hidden rows. SUBTOTAL has an option to not ignore manually hidden rows, but only if not used with filtered rows at the same time!

[@TC_99]

Subtotal ignores other Subtotal functions in the range.

[@saqibkhan7377]

Is this function available in excel 07?

[@MyOnlineTrainingHub]

07!!! Time to update, I think 😉 No, it's only available in Excel 2010 onward.

[@katendefrancis6349]

ln luganda we say, webale nyo! To mean Thank You

[@MyOnlineTrainingHub]

My pleasure 😊🙏

[@GeertDelmulle]

This was a video of a subject “on steroids”. Well done and thank you!

[@MyOnlineTrainingHub]

Thanks so much, Geert!

[@yulinliu850]

❤

[@MyOnlineTrainingHub]

First! Thanks for watching.

[@afzaalahmed9565]

I am not a professional user of excel rather i use excel as a fun. i have an issue though irrelevant to the topic but i think you can do this. How can we handle if a product of two digits cross the limit of 15 digits.

[@MyOnlineTrainingHub]

Happy to help, but please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@afzaalahmed9565]

@@MyOnlineTrainingHub ok thanks, I will upload my excel file and question and wait for solution.

[@HoaNguyen-zz4cn]

♥

[@MyOnlineTrainingHub]

🙏thanks for watching!

[@nairobi203]

Nice pill.

[@7_of_9]

Damn you look even better now than 15 years ago. You age like wine!

[@ericgora2402]

Must be because she's saving her self the stress of misusing Excel for over 15 years

[@MyOnlineTrainingHub]

😁 hidden benefits.

[@funloving2785]

Some aggregate functions like arrays don't work if your admin has limited user's "connected experiences". Excel coughs up this error box: "This command isn't available. Your organization's administrator turned off the service required to use this feature." and your cell has the "#NUM!" error

[@MyOnlineTrainingHub]

That wouldn't apply to this function. That'd be for functions like STOCKHISTORY that reach out to external sources for the data.

[@funloving2785]

@@MyOnlineTrainingHub Well, since I've experienced it directly I know this to be accurate. I'd be happy to show you a screen shot of the error, as well as Microsoft's explanation of what check box to click to correct it.

[@MyOnlineTrainingHub]

Yes, please. I can follow up with Microsoft through my MVP channels. You can email me: website at MyOnlineTrainingHub.com

[@bvds2007]

That’s all good, but this functionality is a couple decades too late. Might as well learn Python/Pandas and free yourself from Excel’s limitations. Having said that, if Excel is a must, then this is very useful.

[@MyOnlineTrainingHub]

Thanks for watching!

[@6lskill103]

Linda, this is the first time I've seen your erroneous conclusion! The Agregat function ignores any hidden lines - whether it's done by a filter or manually, you're wrong!

[@MyOnlineTrainingHub]

I disagree. If you use any of the options numbers that ignore hidden rows (i.e. 1,3,5,7), AGGREGATE ignores rows hidden using the filters OR manually hiding them with right-click. Whereas SUBTOTAL will not ignore rows hidden using right-click > Hide Row when using function numbers 1 through 11.

[@6lskill103]

Alas, your opinion does not change the essence. Everything is exactly the opposite))) I'll be sorry if this offended you. With great respect for your work and experience!

[@19761999]

Mynda, you are pretty.

[@kylelane630]

Talented in her way of sharing knowledge and skills is what I say

[@MyOnlineTrainingHub]

Thank you!

[@l3ol3lca]

Awesome!

[@MyOnlineTrainingHub]

Glad you liked it 🙏