Dynamic array total rows that move automatically? Watch this!

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:

★ About this video ★
In this video, we explore a powerful technique for creating a dynamic total row that updates itself as a dynamic array expands or contracts. This method ensures your total row always stays accurate and in place.

Not only do we look at how to position the total row dynamically, but we also make the calculation type dynamic. Now, users can select what calculation they want to see (SUM, AVERAGE, MIN, MAX etc.).

Say goodbye to manual updates and hello to automation!

0:00 Introduction
0:36 Basic total row
5:08 Dynamic total row
11:58 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[DiveBombDaveTV]

Never knew about Xlookup returning a cell reference, nor being able to enter the arguments for the functions in a separate set of brackets. I've learned a lot here!

[mouhammadwasseemshaabani]

You really have to change your channel name to excel magician, cause you are using excel in magical way, thank you so much

[GregStimpson]

Last night, when i went to sleep, i was good at Excel.
Now, well, it's gonna be a long day.
(This is cool stuff)

[stefankirst]

Mind officially blown 🤯 You`re a digital wizard 🪄

[ziggle]

Excellent exposition! The use of eta reduction is a good demonstration of the possibilities in modern Excel.

[tecwzrd]

Finally a decent example of the SWITCH formula. Generally I'd use a pivot for this but I can see this being useful on a dashboard with drop down selections as it would be dynamic and cleaner.

[kebincui]

Genious application of dynamic array. Thanks Mark 👍

[rdatta]

I was just working on this problem yesterday. The first approach here would be ideal. Very well done!

[IvanCortinas_ES]

Extremely good, as always.
Thanks for continuing to provide robust solutions.

[kdfarmery]

Thank you Mark - that is superb - just need to add drop-down lists for the functions to assist the end user. 👍

[deepaksugandhi]

EXCELLENT solution. I was using helper columns to get through this issue. Thank you Mike.

[RichardJones]

Great work. This is so advanced that even if I became a master at your Excel Academy, I still would not have thought of how to do this! Keep the dynamic tips coming

[adhamm]

Let function has simply given you the access to God mode in Excel. I mean this is very advanced stuff. Amazing!

[patrickschardt]

Wow great solution and explanation.

I like the bit you said at the end about knowing vs referencing… I’d say both are good. Known the solutions you use most often and know how and where to look for guides, tutorials, resources for solutions you use less often

[markpodesta]

Thank you Mark. I hope dynamic conditional formatting comes in very soon.

[jeffstevens]

Excellent as always Mark. I'm not sure if I would have ever thought to pass text results to the argument in an ETA Lambda but now I'm sure I'll use this technique a lot!

[davidabuang]

Great video! Interesting use of BYCOL as the vessel for applying the functions to each column. Incidentally, CHOOSECOLS returns type 64, but INDEX will return type 128 for each of the functions. You could use INDEX and eliminate the need for BYCOL in the TotalCalc definition. For example:

MAP(ColIndex, LAMBDA(v, INDEX(CalcType, v)(CHOOSECOLS(Array, v))))

[grandepatron]

Have not even finished watching and insta like!

[colinekszczecin]

And here I was thinking I was reasonably good with Excel 😆

[Swizzletwice]

Excellent video, as are all your videos. Not sure I could ever think of such a formula on my own, but I was able to follow the logic of it, and it produces a beautifully dynamic total row. Is there an alternative to the CalcType line in the formula for example #2? That part of the formula doesn't seem to work in Excel 2024 due to the eta thing.