No More SUM Errors with the AGGREGATE Function in Excel #shorts

A common problem that can occur in Microsoft Excel is that errors in the data show up in summary calculations. An easy way to avoid this is to use the AGGREGATE function. This is a powerful, multipurpose function that can perform 19 different aggregations like SUM, COUNT, AVERAGE, MAX, MIN, etc. with the option to ignore hidden rows and error values.

Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

[andrewcharlesmoss]

The AGGREGATE function is like a Swiss Army Knife. Very versatile!

[Bill_Woo]

Clever, but beware. If you ignore something, you may neglect to deal with something that needs to be dealt with.
The existence of an error in a sum usually is a signal that the sum is not correct !
However that may not always be true. If E1 = D1 / 1.5 and some of column E is text, and you're confident that E is otherwise valid, then it's a great timesaver. I love this channel !

[stuartduncan]

Great video. I normally avoid "shorts" as they often don't have any narration. This quick, clear guide to Aggregate was very useful and easy to remember. I look forward to more of these.

[Rice]

I still use simple SUM function to control errors. AGGREGATE will not show me where the error is hidden. But with SUM i just have to realize that all data is correct and today i've fixed one in my book.
Of course, i agree that AGGREGATE is more flexible function, but each of them has its own features.

[meleciosantiago]

Love this daily learnings so much...thank you!

[wayneedmondson]

Great Leila. Thanks for the reminder of how useful AGGREGATE can be. Thumbs up!!

[Adam_K_W]

I use this all the time now in place of the formerly used Subtotal function. I wonder why Microsoft didn't just update the Subtotal Function with the Ignore option, as an optional argument so it would be backward compatible...

[petr]

Yes, but it's not educational. We can only ignore mistakes if we know what we are doing. The correct question is: Why did the error occur? For searching we should use IFERROR, treat incorrect inputs, or use the IF function.

[chh]

AGGREGATE Function??? ... Who knew? (Leila did ... of course).

Another helpful nugget ... thank you

[rileyh]

Very cool, didn't know about this!

[suzannedonaldson]

Thanks so much for this Leila - great tip! I have 2 questions for you please, if you don't mind ...
1. Is there a way you can use this with a MINUS SUM, where you are trying to subtract numbers from the others, instead of just addition?
2. If I just wanted the calculation to ignore cells containing text, (as opposed to all errors) would this be the best function to use, or is there a better one?
Thanks so much

[olexanderk]

How do AGGREGATE values from the arrange with the CELLs with formulas? Like where Table2[@[2021]=Index(....

[mihaelastancu]

Hi Leila, I have a technical question :) and I hope you'll help me clear it out: could you please tell me what kind of audio setup you're using for recording your videos? Microphone, recorder, any other important ingredient for obtaining such a wonderful clarity of your recorded sound? Something specific to pay attention to, when going out shopping for this kind of equipment? Any advice on that? :) Thank you very much in advance!

[ruudb]

Very nice! I was not aware it was soooo easy! :-)

[dkoch]

Oh this would've saved me many headaches when building spreadsheets at my old job lol

[alenavaldes]

Thank you Leila. I'm trying to find a formula that will count unique values from a table, and will be dynamic like the aggregate function (ignoring hidden rows). Unfortunately I cannot find any solution in internet. Do you know how to do it? Thank you for your videos!

[-mo]

Hi how can we insert row with some data in same sheet
Example
I have make a table and I want to insert same table below it by clicking one button
I hope you can answer me

[DaveAMcLaughlin]

Merry Christmas/Happy Holidays, Leila! Thank you for all of the amazing tutorials and tips! :)

[Anthony-nvgd]

Can it be used in conjunction with sumif?

[DWerner]

I am trying to pull data from a football schedule for weekly scheduling. Each column has 32 team names. Half of those have the "@" symbol in front of their name to indicate they are the home team. Do you know of an easy solution to pull out ONLY the team names with the "@" symbol before their name, remove the "@" symbol and spill the results into an array with no blank rows?