Nice Video and Solutions Sir!! That indeed is an issue from a long time. One thing I observed is, we can define the range inside LET Function: =LET(rng,G3#,SUMIFS(TAKE(rng,,1),TAKE(rng,,-1),K7)) TAKE/DROP also gives result a reference so they are also acceptable inside SUMIFS. Few months back I made a video on how to determine if result is a reference or not - th-cam.com/video/M1JY51rCcYQ/w-d-xo.html
Brilliant video Mark. It gets confusing sometimes figuring out what works with what in the transition to dynamic arrays last few years. I usually figure it out but takes a few attempts! Group/pivotby will be great.
Great video, I don't have GROUPBY yet! 🙃 I think I would use the below: Cell J2=UNIQUE ( CHOOSECOLS( VSTACK(Table1,Table2), 1 ) ) and =LET(x, VSTACK(Table1,Table2), SUM( FILTER(x, CHOOSECOLS(x, 1) = J2) ))
Wonderful!!!. In this tutorial there was only ONE condition for Bravo, what if we have more than 3 conditions including Text, Numbers, and dates such that, what is the total interest amounts paid to the bank starting from a specific date and less than another specific date.
Thank you! I use dynamic array functions extensively (almost always within LET statements for readability & documentation) & have been frustrated for a long time by the inability to use SUMIFS, et al, with DA functions. I never understood until viewing this video that (virtual) ranges & arrays are different beasts. The “bug” makes sense to me now. I still wish that MS would fix this “bug”, but I’ll labor on without the same level of frustration. Thanks!
It does seem like a "bug". However, even before "dynamic" arrays, this function didn't work with non-dynamic arrays, so maybe it is by design. But so few people used arrays in the past that it rarely came up as an issue.
Very helpful thanks Mark, I remember that I have been facing some issues here. Quick but big question 😅: Is it possible to add a total line at the bottom when we use any spilled function other than GroupBy?
I would try using VSTACK on the result of the spilled range combined with the aggregation we want at the bottom of our spilled range. Something like: =VSTACK(A, SUM(A)) where A is the spilled range we would get as output of some array calculation.
@@maciejkopczynski55 Sure that is an option if you have to aggregate 2 tables. But let say that you’ve use the FILTER function to extract some data and you want to SUM at the bottom (with the text ‘´TOTAL = ´´ without a VBA.
@@maciejkopczynski55 it’s not quite that simple, if you need to be flexible for different numbers of columns. If you use VSTACK to combine arrays of different column widths you get #N/A errors in the empty cells. But you can use to use a MAKEARRAY/LAMBDA combo to create an empty array for the other cells in the total row where there are no values.
Excellent high level explanation. Without a doubt, the introduction of GROUPBY has avoided using the BYROW - LAMBDA combination (always complicated for a user with little practice). Thank you Mark.
There has been discussion as to whether GROUPBY / PIVOTBY will kill PivotTables. I think the answer is No. But it might be the killer of conditional aggregation (SUMIFS/AVERAGEIFS etc).
The video didn't address my original issue, but I did learn what VSTACK is which helped me accomplish what I originally set out to solve, so thank you!
Great video explaining the nuances. However, SUMIFS will work with spilled arrays provided the arrays are 1 dimensional. So in your first example, if you VSTACK the [Item] and [Value] columns separately then the formula =SUMIFS(H3#,G3#,K3) will return the correct result and this will also work if your criteria is a spilled array as well (e.g., =SUMIFS(H3#,G3#,K11#)). The complication only comes in with 2 dimensional arrays.
I think your understanding of ranges and arrays is slightly off. When an array is placed onto a worksheet, it ceases to be an array and becomes a range. So H3#, whether a 1D or 2D, is always a range (you can prove this with the ISREF function). If the spill range is 1D then, Yes, it will work inside SUMIFS because it is a range. If the spill range is 2D then, No, it won't work because even though it is a range, SUMIFS requires a single dimension. This is why using a function, such as INDEX (plus there are many other range functions) works. If it has a range input and returns a range output. However, if it has an array input, it returns an array output. CHOOSECOLS and VSTACK can take ranges or arrays as input, but always return an array.
Thank you. Just what I was looking for. As groupby isn't available yet for non-beta users, I will use the byrow, filter, choosecols. I am creating a 100% dynamic report, so this came in handy
Very useful video. Would have been super nice if it was possible to create a table with the result of the vstack formula in column G-H. That would have simplified the formulas for sumifs. I have particularly missed the possibility to filll a table with the result of a filter-function. Hopefully this is next on the improvements to come in Excel.
Thanks, I'm glad you liked it. Actually, I don't agree about putting an array into a Table. We would be mixing the data step with the calculation step. It might make referencing easier, but in terms of workflow, I don't believe it's the right option.
Fantastic. I've been struggling with this and not finding useful explanations anywhere. Also, I didn't know about '0' in the index formula 🥳 Thank you.
Thank you, Mark! It is a great video and a very clear explanation. The content becomes even more advanced by the end of the video. 😅 As a big fan of Sumifs function, I learned a lot!
I had this video on my watchlist for quite a while. Today I finally took the time to watch it once and will spend the rest of the month watching it repeatedly until I really understand and be able to reproduce what you're presenting. Good stuff, thanks 👍🏼
Hi, really appreciate this valuable video. So i understand that for functions like sumif,countif or filter that involve criteria arguments, the criteria range and criteria cannot be simultaneously spilled arrays, to solve the problem, index is a quite easy and simple method, or use byrow bycol /scan those iterate formulas. I hope what i understand is correct.
It's not about the functions which require criteria arguments. SUMIF, COUNTIF can only use ranges, while FILTER can use arrays. INDEX if used with range input, also returns a range output, so can be used inside SUMIF/COUNTIF. FILTER/SUM doesn't spill so we need to use LAMBDA helper functions to force it to spill.
G3# must be on the face of the worksheet, while VSTACK calculates in memory. So it just depends if you want to take up unnecessary worksheet space just for a calculation.
Data is complicated, so how can we manage that complication to provide the least amount of future re-work. It saves you needing to manually update a formula range. Therefore reduces the risk of error. Which in turn it prevents the person who presented the information looking like a fool when they discover that the values don't add up, and then having to admit to the CFO why the budget was wrong... (just saying 😁)
For these simple examples, the proposed cobinations of functions are convoluted. But they explain nicely how the different functions work and how to use them in situations where they will indeed be the easiest solution. Thanks Marc.
@@farooqtahir538 which part is over complicated? If the xxxxIFS functions supported arrays, we wouldn’t have these issues. But they don’t, so how would you solve it?
Nice Video and Solutions Sir!! That indeed is an issue from a long time.
One thing I observed is, we can define the range inside LET Function:
=LET(rng,G3#,SUMIFS(TAKE(rng,,1),TAKE(rng,,-1),K7))
TAKE/DROP also gives result a reference so they are also acceptable inside SUMIFS.
Few months back I made a video on how to determine if result is a reference or not - th-cam.com/video/M1JY51rCcYQ/w-d-xo.html
Very cool technique! Thanks for sharing!
Yes, agree 100%.
Lol, didn't know that. Great hint, thanks a lot!
…good stuff….my head will stop spinning by the end of next week…
I’ll have a new video out by then… you head might never stop spinning 😂
Excellent! I am excited for when my update channel gets GROUPBY. Thanks, Mark!
I think it might be a while off yet.
Thank you, Mark. Truly awesome. And thank you for your clear presentation style in all your videos.
Thanks Jim. I appreciate that feedback 😁
Brilliant video Mark. It gets confusing sometimes figuring out what works with what in the transition to dynamic arrays last few years. I usually figure it out but takes a few attempts! Group/pivotby will be great.
Agreed. The more I look into it, the more I understand why things don't work. But it get's very complicated very quickly.
🎉🎉🎉🎉i was encounter this problem, and i used sum filter, know i know more alternative thanks ❤
SUM/FILTER is the way I would go. However GROUPBY might become the easiest alternative.
NEVER condition, when you offer something!!! (download file.....)
I'm not making you download the file, I show you everything on the video.
It's hard for you to complete ....
I wonder if it's hard for you to show the video too
Great video, well explained where the problem is and how different ways could look like.
Thanks, I'm glad you found it useful.
Great video, I don't have GROUPBY yet! 🙃
I think I would use the below:
Cell J2=UNIQUE (
CHOOSECOLS( VSTACK(Table1,Table2), 1 )
)
and
=LET(x, VSTACK(Table1,Table2),
SUM(
FILTER(x, CHOOSECOLS(x, 1) = J2)
))
Thanks. I was able to apply this in one of the spreadsheet tools I made. Well explained and really helpful.
Mark, great video. Can you give an example where you sum based on two criteria?
That's just about understanding the FILTER function.
FILTER([data],([column1]="XXXX")*([column2]="YYYY"))
@@ExcelOffTheGrid I have a situation where I need to SUMIFS a table by a spilled array of dates and the value in a one cell. How can I do this?
Wonderful!!!. In this tutorial there was only ONE condition for Bravo, what if we have more than 3 conditions including Text, Numbers, and dates such that, what is the total interest amounts paid to the bank starting from a specific date and less than another specific date.
Thank you! I use dynamic array functions extensively (almost always within LET statements for readability & documentation) & have been frustrated for a long time by the inability to use SUMIFS, et al, with DA functions. I never understood until viewing this video that (virtual) ranges & arrays are different beasts. The “bug” makes sense to me now. I still wish that MS would fix this “bug”, but I’ll labor on without the same level of frustration. Thanks!
It does seem like a "bug". However, even before "dynamic" arrays, this function didn't work with non-dynamic arrays, so maybe it is by design. But so few people used arrays in the past that it rarely came up as an issue.
could you do choose then have a sumif criteria in that
Yes, CHOOSE is also a function which returns a range. So that would also work in a SUMIFS.
Excellent explanation! Thank you for your efforts! 🎁🎁🎁
You're very welcome!
Any option that array functions could work inside the table as they throw #spill error
Great presentation. Very clear. Please continue bringing these types of tutorials. Thank you. and subscribed!
Thank You. I will do my best.
Instead of the SUMIFS function, you can also use the regular SUM function: =SUM( CHOOSECOLS(G3#,2) * ( CHOOSECOLS(G3#,1) = "Bravo" ) )
Now try it with AVERAGE… is it the correct value?
Very helpful thanks Mark, I remember that I have been facing some issues here.
Quick but big question 😅: Is it possible to add a total line at the bottom when we use any spilled function other than GroupBy?
Yes, absolutely. Let me add that to my video list. I try to make that soon.
I would try using VSTACK on the result of the spilled range combined with the aggregation we want at the bottom of our spilled range. Something like:
=VSTACK(A, SUM(A)) where A is the spilled range we would get as output of some array calculation.
@@maciejkopczynski55 Sure that is an option if you have to aggregate 2 tables. But let say that you’ve use the FILTER function to extract some data and you want to SUM at the bottom (with the text ‘´TOTAL = ´´ without a VBA.
@@ExcelOffTheGrid Perfect, thanks Mark !
@@maciejkopczynski55 it’s not quite that simple, if you need to be flexible for different numbers of columns. If you use VSTACK to combine arrays of different column widths you get #N/A errors in the empty cells. But you can use to use a MAKEARRAY/LAMBDA combo to create an empty array for the other cells in the total row where there are no values.
Excellent high level explanation. Without a doubt, the introduction of GROUPBY has avoided using the BYROW - LAMBDA combination (always complicated for a user with little practice). Thank you Mark.
There has been discussion as to whether GROUPBY / PIVOTBY will kill PivotTables. I think the answer is No.
But it might be the killer of conditional aggregation (SUMIFS/AVERAGEIFS etc).
The video didn't address my original issue, but I did learn what VSTACK is which helped me accomplish what I originally set out to solve, so thank you!
Thanks for sharing!
Great video explaining the nuances. However, SUMIFS will work with spilled arrays provided the arrays are 1 dimensional. So in your first example, if you VSTACK the [Item] and [Value] columns separately then the formula =SUMIFS(H3#,G3#,K3) will return the correct result and this will also work if your criteria is a spilled array as well (e.g., =SUMIFS(H3#,G3#,K11#)). The complication only comes in with 2 dimensional arrays.
I think your understanding of ranges and arrays is slightly off.
When an array is placed onto a worksheet, it ceases to be an array and becomes a range. So H3#, whether a 1D or 2D, is always a range (you can prove this with the ISREF function).
If the spill range is 1D then, Yes, it will work inside SUMIFS because it is a range.
If the spill range is 2D then, No, it won't work because even though it is a range, SUMIFS requires a single dimension.
This is why using a function, such as INDEX (plus there are many other range functions) works. If it has a range input and returns a range output. However, if it has an array input, it returns an array output.
CHOOSECOLS and VSTACK can take ranges or arrays as input, but always return an array.
A very handy video, thank you
You're welcome!
Thank you.
Just what I was looking for. As groupby isn't available yet for non-beta users, I will use the byrow, filter, choosecols.
I am creating a 100% dynamic report, so this came in handy
Glad I could help! 👍
Very nice - Thank you for this great Vdieo !!!
Glad you enjoyed it.
Very useful video. Would have been super nice if it was possible to create a table with the result of the vstack formula in column G-H. That would have simplified the formulas for sumifs. I have particularly missed the possibility to filll a table with the result of a filter-function. Hopefully this is next on the improvements to come in Excel.
Thanks, I'm glad you liked it.
Actually, I don't agree about putting an array into a Table. We would be mixing the data step with the calculation step. It might make referencing easier, but in terms of workflow, I don't believe it's the right option.
Fantastic. I've been struggling with this and not finding useful explanations anywhere. Also, I didn't know about '0' in the index formula 🥳 Thank you.
Hi Thanks for the video. Can you please give the link to your previous video you mentionned at 09:42 regardin de #N/A error ? Thanks
Here you go: th-cam.com/video/AgAVMTbNDYs/w-d-xo.htmlsi=EBmo898dVP6n1UuR
Great tip and very well explained, thanks
Glad it was helpful!
Thanks man, super helpful
Is there a way to recreate this in Google Sheets?
Thank you Mark for your always valuable info and tricks you are providing to us...
My pleasure!
Thank you, Mark! It is a great video and a very clear explanation. The content becomes even more advanced by the end of the video. 😅 As a big fan of Sumifs function, I learned a lot!
Thanks.
Towards the end I decided to give solutions rather than explanations, otherwise it could have been a very long video. 😁
@@ExcelOffTheGrid Thank you for your kind response. I have grabbed the logic :)
I had this video on my watchlist for quite a while. Today I finally took the time to watch it once and will spend the rest of the month watching it repeatedly until I really understand and be able to reproduce what you're presenting. Good stuff, thanks 👍🏼
Great stuff - I hope it all makes sense. 😁
Brilliant! Thank you for this solution. I have run into this problem many times.
Glad it helped 👍
Brillaint as always. Thanks Mark 👍❤
Thanks Kebin 😁
Hi, really appreciate this valuable video. So i understand that for functions like sumif,countif or filter that involve criteria arguments, the criteria range and criteria cannot be simultaneously spilled arrays, to solve the problem, index is a quite easy and simple method, or use byrow bycol /scan those iterate formulas. I hope what i understand is correct.
It's not about the functions which require criteria arguments.
SUMIF, COUNTIF can only use ranges, while FILTER can use arrays.
INDEX if used with range input, also returns a range output, so can be used inside SUMIF/COUNTIF.
FILTER/SUM doesn't spill so we need to use LAMBDA helper functions to force it to spill.
thanks for the reponse. appreciate the explaination.@@ExcelOffTheGrid
Genius!!! Thanks from South Africa.
Thanks Chris 😁
Master...!
I can only share what I know or discover...😁
This is Amazing ... I already had these problems with sumifs ... now i know ... Many Thanks
You're welcome.
very good example with good options - thank you :-)
You are welcome!
Excellent :)
Thank you! Cheers!
thanks a ton!
Very well explained Mark, thank you.
Good explanation.
Glad it was helpful!
why use vstack over g3#?
G3# must be on the face of the worksheet, while VSTACK calculates in memory. So it just depends if you want to take up unnecessary worksheet space just for a calculation.
is this for MS 365? I tried using the hash after the normal cell reference. it just returns an error.
Excel 2021 has some dynamic array functionality, Excel 365 has all the functionality. Anything Excel 2019 and before and you won't have anything.
@@ExcelOffTheGrid tried it with excel 2021. even tried the G3#.
Incredible! Thanks so much, Mark! Great tutorials, elegant and clear presentation! Congratulations!
Amazing. Thanks. It can be a little tricky to understand, so I'm glad it was clear 👍
Aboslutely BRILLIANT and so perfectly timed for me - thank you!
Great news 👍
Thank you Mark.
You're welcome. 👍
Great Sr..
Thank You.
If you want to make things unnecessarily complicated
Data is complicated, so how can we manage that complication to provide the least amount of future re-work.
It saves you needing to manually update a formula range. Therefore reduces the risk of error. Which in turn it prevents the person who presented the information looking like a fool when they discover that the values don't add up, and then having to admit to the CFO why the budget was wrong... (just saying 😁)
For these simple examples, the proposed cobinations of functions are convoluted. But they explain nicely how the different functions work and how to use them in situations where they will indeed be the easiest solution. Thanks Marc.
@@ExcelOffTheGrid Fool is the person who makes simple things unnecessarily complicated like you are😄
@@farooqtahir538 which part is over complicated?
If the xxxxIFS functions supported arrays, we wouldn’t have these issues. But they don’t, so how would you solve it?