Excel Dynamic Array Totals

[@malejandrahorvath]

This is so great, can't wait to have it available to all 365 users. Thanks Wyn!

[@AccessAnalytic]

Cheers Alejandra

[@wayneedmondson1065]

Hi Wyn. This is a welcome new feature. My current method of building a formula based dynamic crosstab table (against your data) is this: Rows Headings: =LET(x,UNIQUE(Table1[Department]),IF(SEQUENCE(COUNTA(x)+1)>COUNTA(x),"TOTAL",SORT(x))) Column Headings: =TRANSPOSE(LET(x,UNIQUE(Table1[Year - Month]),IF(SEQUENCE(COUNT(x)+1)>COUNT(x),"TOTAL",SORT(x)))) Table Data: =IF((J4#="TOTAL")*(K3#="TOTAL"),COUNTA(Table1[Department]),IF(J4#="TOTAL",COUNTIFS(Table1[Year - Month],K3#),IF(K3#="TOTAL",COUNTIFS(Table1[Department],J4#),COUNTIFS(Table1[Department],J4#,Table1[Year - Month],K3#)))) The above makes for a truly dynamic crosstab with the totals at the bottom and right and the grand total in the lower right. It will expand or contract as you change the underlying data. It requires some conditional format tricks to highlight, bold and or underline the TOTAL rows and columns. While the BYCOL and BYROW Lambdas make for a new way to get the column and row totals, their dynamic placement at the bottom and the right of the calculated data is still not dynamic unless flowed back into the kind of construct I have above. What would be ideal is a CROSSTAB function that provides you the necessary arguments to add totals, if desired, in the same way you can add or remove them with a Pivot Table. In the mean time, we have to do it with the formula gymnastics illustrated. Thanks for demonstrating the new functions. Hope they are released soon. Thumbs up!!

[@AccessAnalytic]

All sorts of fun and games Wayne!

[@OzduSoleilDATA]

Very very interesting. Thanks for such a clear explanation. 🙏

[@AccessAnalytic]

Cheers Oz

[@victor_wang_1]

Definitely a cool new feature. I'd love to see some use cases that a pivot table wouldn't cover.

[@AccessAnalytic]

I think the main benefit over pivots will be automatic recalc and dynamically linked formulas referencing the array range.

[@IvanCortinas_ES]

Interesting improvement in the calculation of totals.Thank you Wyn!

[@AccessAnalytic]

You’re welcome Iván. It is an important formula addition.

[@peterbartholomew7409]

Nice video. There was me thinking you were first and foremost a PowerQuery guy! One observation is that the arguments of both the BYROW and BYCOL functions are identical, so naming them, you get = BYROW(count#, SUMλ) = BYCOL(count#, SUMλ) where SUMλ is the named Lambda function =LAMBDA(x, SUM(x)) BTW I insert the Greek letter into the name using autocorrect because it reminds me that it is a Lambda function, and distinguishes the it from the built-in SUM function.

[@AccessAnalytic]

Thanks Peter. I guess if you go down the naming lambda route it's worth going the whole way and having =ArrayColumnTotal(M8#) where ArrayColumnTotal =LAMBDA(_DARange, BYCOL( _DARange, LAMBDA(x,SUM(x) ) ))

[@AccessAnalytic]

Oh… and yes… first and foremost I’m a Power Query guy 😂

[@drsteele4749]

Wyn, this really helped! This was really useful. I have dozens of XL files that use the splendid dynamic arrays to sum and average things, but then I have to constantly maintain the totals by copying or removing formulas when data is added or deleted. Super!

[@AccessAnalytic]

Thanks Donald

[@taherulhassan]

Your explaining is the best. Keep it up.

[@AccessAnalytic]

Thank you Taherul. Kind of you to leave feedback

[@patrickschardt7724]

Another great tutorial. These functions took Excel to another level

[@AccessAnalytic]

Thanks Patrick. Yes they certainly do

[@helmanfrow]

I was today years old when I learned about the hash feature for referencing an entire array.

[@AccessAnalytic]

Every days a school day with Excel! Cheers Helman

[@helmanfrow]

@@AccessAnalytic To be fair I abandoned Excel about 12 years ago when I moved to Google Sheets for essentially everything. Recently I had to start using Excel again (because, reasons) and I have to tell you it feels like getting out of a Tesla and getting behind the wheel of a preserved Model T. Or maybe, it feels like I've just moved back into an old apartment with structural issues but with new drywall and paint.

[@EricHartwigExcelConsulting]

This is awesome Wyn! Thank you so much for creating/sharing this video!

[@AccessAnalytic]

Thank you for watching and commenting Eric 😄

[@YouExcelTutorials]

Thanks Wyn, your channel keeps me up to date!

[@AccessAnalytic]

Thanks Bunmi

[@jazzista1967]

Hi Wynn! This was a great example... I have been a little bit intimidated to understand the LAMBDA function however , i am going to give it a go so that i can use those 2 helper functions. Quick question: Can you put the BY COLUMN function below the table like a grand total? Thanks

[@jazzista1967]

Maybe not? Because it may cause a SPILLERROR when the table resizes?

[@AccessAnalytic]

That’s right. You can put it below if you don’t intend for the main array to expand.

[@FredericLEGUEN-Excel]

Great explanations Wyn 👍

[@AccessAnalytic]

Thanks

[@zzota]

Thanks Wyn. When my archaic department moves away from manually-written spreadsheets, this will be really useful. Or perhaps I should just get a new job? 🤣

[@AccessAnalytic]

Hah! To be honest companies who don’t get on to 365 will struggle to recruit the more advanced / more efficient Excel users

[@utubeAgape]

Excellent - I am going to use this for sure! Thank you!!

[@AccessAnalytic]

Cheers Irene

[@JonathanExcels]

Good video. I think this still feels like a workaround.

[@AccessAnalytic]

Thanks Jonathan, are you thinking there should be a specific function?

[@JonathanExcels]

@@AccessAnalytic it does somehow need to be easier. As it’s own function or perhaps as an additional argument in the array formula.

[@AccessAnalytic]

Yeah, I know what you mean. I think they’ve gone down this path of developing multi - use helpers so a single “pattern” can now be used to give SUM, MAX,MIN, AVG, COUNT etc…

[@peterbartholomew7409]

No, it's not just a workaround. It is more demanding in programming terms, but it allows you to apply any formula to each column in turn. For example, it would allow you to calculate a set of medians or a comma-separated list representing each column. The construct serves to enhance any aggregation operator.

[@filipwinski4219]

Great!! Thank you for sharing. Is LAMBDA function generally available (not only for insiders)?

[@AccessAnalytic]

Not quite yet released on Current Channel

[@stevereed5776]

Thanks for a clear explanation. I'm having issues running bycol/byrow with the filter function, is it possible to to use these functions with the filter function?

[@AccessAnalytic]

Works for me. Is your FILTER array just values?

[@stevereed5776]

@@AccessAnalytic Thanks for your reply. So I have a unique list of names in column A (using the unique function from a table) and I want to show all products for each person. Filter does not allow the SPILL operator (#) to be used to tring to use either MAP or BYROW. =BYROW(A21#,UNIQUE(FILTER(Table1[product_line],ISNUMBER(XMATCH(Table1[User Full Name],A21#))))) - Just get a #VALUE ERROR. Any advice would be appreciated. Thanks

[@AccessAnalytic]

Unfortunately you can’t do arrays of arrays

[@stevereed5776]

@@AccessAnalytic appreciate the feedback

[@AlphaWatt]

Totally awesome. Thank you sir!

[@AccessAnalytic]

No worries 🙂

[@irinaostapchuk9085]

This is very understandable tutorial. Is it possible to give the link to excel example used in the videos.

[@AccessAnalytic]

Here's a link to the file I used. As at December 2021 you still need an install of the Insider Version of Excel for this to work... aasolutions.sharepoint.com/:x:/g/AA_CPort/EYszVUGjxQtFhl8jX91TuIEBEaB3lyQ8WjdqFtgVXK4oaA?e=rn0TbR

[@irinaostapchuk9085]

@@AccessAnalytic Thank you!!!

[@AccessAnalytic]

You’re welcome Irina

[@realpulsecoin]

Great content, and easy explained! 👏

[@AccessAnalytic]

Thanks Rasmus

[@raphaelwilhelm2605]

Great stuff Wyn!

[@AccessAnalytic]

Cheers Raphael

[@elhashimy]

Amazing, thank you

[@AccessAnalytic]

You’re welcome, thanks for taking the time to leave a kind comment

[@ajit555db]

Excel dynamic array range/functions and lambda functions is going to push more than half of analytics companies out of market.

[@AccessAnalytic]

I think Power BI and the Excel Data Model is already doing that too

[@apostolos55goulandris97]

Nop, didn't help at all, not right now. :D But these 3 Functions are just FANTASTIC!!!! and possibilites are endless. Why does Lamda reminds me of map in JS??? THANKSSSSSSSSSSSSSSS

[@AccessAnalytic]

One day...

[@garyallan69]

Is there a way to total these without the ByRow or ByCol? I have 365 but can't subscribe to the insider because its a company license. I still need to use the dynamic array though?

[@AccessAnalytic]

Hi Gary, my colleague Jeff came up with a way here at the 5 minute mark kzbin.info/www/bejne/oaXUg32KjMp3a9E

[@garyallan69]

@@AccessAnalytic Thank you very much. I was actually playing around with mmult but just didn't get it right

[@shahzad4455]

What if I want to put totals at the bottom (after Warranties) row ?

[@AccessAnalytic]

The table will show a #SPILL error if the data area clashes with the total. You could likely write a much longer formula with LET and IF referring to some row count so that IF number of items < X then calculate the output else calculate the total.

[@josephinepelle8590]

Great!

[@AccessAnalytic]

Thanks

[@alializadeh8195]

Thanx

[@AccessAnalytic]

You’re welcome

[@stevevuoso8411]

Is there a way to put the totals at the bottom and the right?

[@AccessAnalytic]

There probably is with the new VSTACK and HSTACK functions as otherwise I don't think you can avoid the SPILL errors when the range re-sizes.

[@stevevuoso8411]

@@AccessAnalytic Yes, I haven’t been able to find a way to make the sum formulas move with the spill. It would be nice to create a quasi-pivot table that doesn’t need to be refreshed.

[@AccessAnalytic]

e.g. this adds column totals to a simple SEQUENCE array. Pretty horrid formula though = LET( _MainCalc, SEQUENCE(3,6), _ColTotal,BYCOL(_MainCalc,LAMBDA(col,SUM(col))), _CALCwithColTotals, VSTACK(_MainCalc,_ColTotal), _CALCwithColTotals )

[@amrtawfik4878]

Hello Dear, i wasn't able to to download this excel for practicing purpose would you please help to pass it to me.

[@AccessAnalytic]

Try this aasolutions.sharepoint.com/:f:/g/AA_CPort/EmNGOJdpGStEsV9w9z3lCggBPT_T7ZbIeBa9se-gJysW8Q?e=SRtQbA

[@xiaojienan7891]

why donot u just insert pivot instead? Confused

[@AccessAnalytic]

Most of the time I would use a Pivot but if it’s safer to have automatic updates or use the data to feed another calculation then this would be my choice