Advanced Formula Magic: Running total by row with dynamic arrays in Excel

★ 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 ★
Some scenarios in Excel seem easy. However, one small change can lead to the wrong result.

In this video, we are looking at one of those scenarios. It starts out as a simple running total and ends up as an advanced function used LET, MAKEARRAY, LAMBDA, SUM, FILTER.

INCLUDES:
0:00 Introduction
0:18 Scenario
0:42 Basic solution
1:42 Problem with the basic solution
2:10 Calculating position
3:29 SUMIFS alternative
4:46 Understanding MAKEARRAY
5:52 Using MAKEARRAY
6:34 Using LET to simplify the formula
9:29 Calculate by column
9:50 Wrap-up

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

★ Where to find Excel Off The Grid ★

#MsExcel

[jenniferdustin]

I'm a BIG fan of the Let function. I'd love to see a video on just that, and all the many ways it can be helpful. (and its faults too)

[Excelambda]

Great video !! Great solution !!
For fun, we can change the order, in general, adding index rules and still use native GROUPBY and/or PIVOTBY results. (remove rules after if we want)
Also for running totals good old MMULT can do the trick.
step 1. extract inner array of the pivotby function; step 2: running tot MMULT(array, seq(4)<=seq(, 4))
All this in a formula for our case:
=LET(p, PIVOTBY(B4:B19, XMATCH(C4:C19, G10#)&C4:C19, D4:D19, SUM, , 0, , 0), c, COUNTA(G10#), MMULT(DROP(IFERROR(--p, 0), 1, 1), --(SEQUENCE(c)<=SEQUENCE(, c))))

[davescrams]

Excellent demonstration of building up a solution and then refining the implementation for ease of understanding and reuse.

[kebincui]

Brilliant and creative as always 👍. Thanks Mark for sharing your tips which are all gems ❤

[viktorasgolubevas]

Perfect! Generator MAKEARRAY is a very, very useful function...
With dynamic arrays, we can simply SUM using (if/where) criteria, thus syntactically removing FILTER, eg
=MAKEARRAY(COUNTA(F10#), COUNTA(G9#),
LAMBDA(r, c,
SUM( Data4[Value] * (r>=XMATCH(Data4[Item], F10#)) * (c=XMATCH(Data4[Region], G9#)) )
))

And... I call this implementation "Running total by row" :), ie column is fixed in the formula

[stanTrX]

This looks something super advanced and i dunno if i use it or not in my works but if i have to, thanks in advance for such solution

[IvanCortinas_ES]

Excellent work, Mark. Absolutely logical in the process. Thank you very much for sharing these cases, as they respond to real situations in the company.

[williamarthur]

Thanks, not used Makearray before, knew what it did just never really had a use.
I did have a go at a dynamic version before watching, made the mistake of trying to put a let within Lambda, which mean it was trying to refer to something 'outside', got there in the end.

[prasoft]

As usual, very very informative video(s).Thank you, Sir🙏 you are my guiding star.

[GeertDelmulle]

Great solution, Mark, for a tough problem. I think your solution is quite efficient.
Yet, I respectfully disagree with the premise: those regional headers should not be used for cumulative sums because those results are misleading for the consumer of the report.
But of course, this was only an example to prove a point. Again, well done. 👍

[wakeenaushad]

Just a quick thought, we could use CHOOSECOLS and CHOOSEROWS with the r and c parameters of make array to get the row names and columns names to be used inside the filter criteria . Would shorten the syntax by quite a bit

[jhonathanalexandergonzales]

Awesome! Definitely I'm gonna try it!

[asheeshpahwa]

Amazing Mark!! Loved your solution. I tried to get this done with nested reduce and Scan but i found Makearray very powerful. I had to use double reduce to iterate through Item and then region but it was fun. Keep bringing these vedios. Thanks a lot.

=DROP(REDUCE("", UNIQUE(F6:F21), LAMBDA(x, y, VSTACK(x,
LET(f, FILTER(G6:H21, F6:F21=y),
r, DROP(REDUCE("", UNIQUE(G6:G21), LAMBDA(a, v, HSTACK(a,

[GiorgioBerardi]

Maybe not the easiest syntax, but explained brilliantly!

[Ozgur-wqsq]

Brilliant and please share more content with filter() function magic.

[NestorCirhuza]

Hello @Marc, can this formula works when rhe data set is in a range instead of a table?

[att]

Mark, thanks for sharing the excellent solution. I learnt a lot from you. I have couple of questions:
a. During the videio, you put "<=" & G3#. I am wondering why you not just G3# only, why did you use "<=" ?
b. I am wondering if I can get your help on my actual worksheet. I have a table of data by category (in a column) and by monthly (in a row header), similar to your table, by Alpha, Bravo & Charlie and by Q1...Q4. The only thing different from your example is your original data in the columar format, but mine are in table as original data. I would like to have the same table with category in a column and by yearly. Basically, summarize the data by yearly. Is there any way to have the new table with spilled array for the data? Thanks, appreciated!

[RonDavidowicz]

Another great video.

Question on the lambda function: in this formula you define r & c in the first part of the lambda functions, and then reference the r & c later in the function, but how does lambda “know” that r is 3, and c is 4?

[arpwable]

This is a beautiful construction, and very well explained, but I'm struggling to see why it'd be worthwhile to do this rather than a pivot table that could do the same thing...

[davidabuang]

Nice use of MAKEARRAY here. MMULT can also accomplish it in one go using the same logic...

=MMULT(--(TOROW(MATCH(Data[Item], F11#, 0))=SEQUENCE(ROWS(F11#))), (MATCH(Data[Region], G10#, 0)<=SEQUENCE(, COLUMNS(G10#)))*Data[Value])

Can be tricky to learn, but it's really just a matter of getting the x and y axis in the right direction. ;)