Simple Dynamic Array Formula Question

[RICH937]

I'm a Dynamic Array newbie, and this question is more about formula structure than needing help. I have a dynamic array (DK4#) that I wanted to sum the values entered in the 4th column. Based on my understanding of new/old dynamic array formulas, these two formulas should produce the same results:

SUMIFS(CHOOSECOLS(DK4#,4),CHOOSECOLS(DK4#,1),DK4)
SUMIFS(INDEX(DK4#,,4),INDEX(DK4#,,1),DK4)

However, the first formula returns an error (value#), but the second works fine. Anyone know what makes excel see them as different?

 

[Alex Blakenburg]

Index returns a Range object although most people only ever use the value in the range being returned.
(Note: XLookup also returns a Range Object although normally in common use only a single cell)
In VBA you could use Index or XLookup to get the address of what they find)
ChooseCols return an array and not a range.

Why that is important is because SumIfs will only work with Ranges and not Arrays.
So it is fine with Index but won't handle an ChooseCols (or any other array formula).

Just to see what it can look like

 

[Alex Blakenburg]

If you want to use ChooseCols then you could use this instead:

SUM(CHOOSECOLS(DK4#,4) * (CHOOSECOLS(DK4#,1) = DK4))

 

[RICH937]

This is so much simplier. Brilliant. Thank you. What is frustrating is while there's a TON of vids on YouTube and excel sites online, there is a wealth of info on using both new/old formulas with data arrays (as well as tables/ranges). Nearly nothing on applying those formulas using the hash (#) references. A lot of them work the same in both, but not all. When linking analysis to a data array drawing from a table, database, or other source that is updated, not using "#" means one can easily end up with formulas that contain way too many calculations for Excel to handle efficiently....or at all. It's crazy frustrating when a formula using A:C or $A$1:$C$50000 works fine, but A1# returns a #VALUE! error.

 

[Alex Blakenburg]

Glad you found it helpful. Thanks for the feedback.

 

[Peter_SSs]

I know that your question was about why a particular formula did not work, and Alex has answered that, but another way with CHOOSECOLS would be

=SUM(IF(CHOOSECOLS(DK4#,1)=DK4,CHOOSECOLS(DK4#,4)))