Great stuff - I hope it all makes sense. 😁

Thanks Jim. I appreciate that feedback 😁

I think it might be a while off yet.

SUM/FILTER is the way I would go. However GROUPBY might become the easiest alternative.

You're welcome!

Thanks Kebin 😁

My pleasure!

Glad you enjoyed it.

Glad it was helpful!

You're welcome. 👍

Great news 👍

Thanks Chris 😁

Amazing. Thanks. It can be a little tricky to understand, so I'm glad it was clear 👍

You are welcome!

You're very welcome!

Thank You. I will do my best.

Glad it helped 👍

You're welcome.

Thank You.

Glad it was helpful!

Thanks, I'm glad you found it useful.

Agreed. The more I look into it, the more I understand why things don't work. But it get's very complicated very quickly.

Thank you! Cheers!

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 :)

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.

I’ll have a new video out by then… you head might never stop spinning 😂

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.

Glad I could help! 👍

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).

I can only share what I know or discover...😁

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.

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

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?

Now try it with AVERAGE… is it the correct value?

Yes, CHOOSE is also a function which returns a range. So that would also work in a SUMIFS.

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.

Very cool technique! Thanks for sharing!

Yes, agree 100%.

Lol, didn't know that. Great hint, thanks a lot!

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.

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#.

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

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?