Microsoft Excel - GroupBy with Multiple Aggregations (plus, Dynamic User Selection)

[@IvanCortinas_ES]

Excellent construction with the INDIRECT function to identify the data validation category. The sorting part with the IF function is also very brilliant. Thanks for sharing.

[@bcti-bcti]

We appreciate your kind words. Thank you.

[@Quidisi]

Your teaching methods are fantastic. It's one thing to know how to do something. It's quite another thing to be able to simplify and explain to others. And I love how you did a recap, at the end. Someone once gave me this advice, when teaching: "Tell them what you are going to tell them. Tell them. Tell them what you told them."

[@bcti-bcti]

@@Quidisi Thabk you so much for your kind words. I’ll try to keep up the quality to your liking. Thanks for watching.

[@iankr]

This is a great tutorial. Combining this many of Excel's relatively new spilled array functions can produce results that a few years ago would have required VBA or goodness knows what horrendous combination of old-school functions! Many thanks for this. As ever, clearly and succinctly explained. Ian, UK

[@bcti-bcti]

I absolutely LOVE the new dynamic array functions and spilled arrays. Between these and Power Query, my VBA needs have dropped by about 90%

[@ExcelWithChris]

Brilliant as always. Greetings from South Africa.

[@bcti-bcti]

@@ExcelWithChris Hey, Chris! Always glad to hear from you. Hope all is well.

[@DingusBatus]

When I was taught Excel at college back in 2009/2010 any more than 2 nested functions would melt my brain. Now a combination of watching videos like this + just playing around I can just about keep up with the 5+ nested functions, and tricks you used here.❤ The way you start off simple then slowly explain each additional function added. ❤

[@sujirpinna]

By Far the best video on thinking outside the box and implementing it on excel :)

[@bcti-bcti]

@@sujirpinna Thank you!!!!!

[@gavin.d.m]

Great video. I can see me using Groupby over Unique/Sumifs.

[@bcti-bcti]

@@gavin.d.m Thanks. Glad it provided some inspiration. We appreciate you taking the time to watch.

[@RichardJones73]

Good work and well explained. But it'll be out of my head as soon as I try to remember how it's done when I need it

[@bcti-bcti]

@@RichardJones73 You can always rewatch (and increase my view count) 😁

[@grahamc5531]

For the conditional formatting and the border formats - select the second item in the initial "New Formatting Rule" dialog box - "Format only cells that contain"....in the bottom half of the dialog box, choose "No Blanks" in the "Format only cells with" drop-down list. You can then apply the required formatting as normal...

[@bcti-bcti]

Damn! Why didn't I think of that? That is SO MUCH easier than my method. 1,000 thumbs up for letting us know. Thanks for watching.

[@medher3593]

great tutorial, much appreciated

[@bcti-bcti]

@@medher3593 you are quite welcome. Thanks for taking the time to watch.

[@Luciano_mp]

Great tutorial, thank you!

[@bcti-bcti]

@@Luciano_mp you are most welcome!

[@excel_along_the_way]

Thank you for the video. You should also give the alternative to INDIRECT. Althouth it would make the formula more complicated I would have used the SWITCH function, What a lot of people don't show is that formulas like SWITCH and CHOOSE can also return ranges.

[@bcti-bcti]

@@excel_along_the_way Can you provide an example? I’d love to see how you solve this. Thanks.

[@excel_along_the_way]

​@@bcti-bctiinplace of INDIRECT following: SWITCH(I2,"Product",Sales[Product],"State",Sales[State],"Region",Sales[Region],"Supplier",Sales[Supplier])

[@bcti-bcti]

@@excel_along_the_way That is definitly a way to avoid the use of INDIRECT. I think I would still use INDIRECT in this scenario as it is not tied in any way to the names of the fields or the number of fields presented as grouping options. Thanks for your input. I always like to see how others solve the same issue. Keep up the great work!

[@excel_along_the_way]

@@bcti-bcti here is another one that I wanted to try and I think you will convert to: XLOOKUP(I2, Sales[#Headers], Sales)

[@bcti-bcti]

@@excel_along_the_way I believe you have solved the “no INDIRECT” puzzle the best way!!! Great job. I wish I had thought of that. Thanks for helping me be better.

[@GeertDelmulle]

Great video, but two questions: 1. Why not use CHOOSECOLS instead of INDIRECT? 2. For the conditional formatting of the cells (border) you can use a predefined function (is not blank) instead of a general custom function that does the same. Do you agree?

[@bcti-bcti]

As for your second question: are you recommending something like "=NOT(ISBLANK(H6))"? And your second question: can you provide an example of how you would solve the INDIRECT replacement issue? I prefer to not use INDIRECT if possible, so I'm very open to suggestions. Thanks for watching and participating in the conversation.