Forum Discussion
Sumproduct and New Dynamic Array Formula
By the way, what is the reason for using of =INDEX($A$5#,SEQUENCE(ROWS($A$5#),1,1,1),1) instead of =$A$5# ? They shall return the same.
Thanks Sergei,
Because I only want to work with first column of the data and $A$5# is a three column range.
My real issue here is that if used in separate column =INDEX($A$5#,SEQUENCE(ROWS($A$5#),1,1,1),1) is returning proper range (see Column G), but when wrapped in SUMPRODUCT, it is returning only First value (Only First Value appears using F2 and then evaluated using F9, which seems incorrect).
While using Evaluate Formula, I can see that the full range returned by the above formula is being evaluated but not in the way I desire.
Why does the behaviour of SEQUENCE changes, when wrapped in SUMPRODUCT ?
What I wish to do is to place the formula in Cell E5, so that it returns the Sum for all the BankID mentioned in range A5:A30 in range E5:E30.
I understand that there must be a LAMBDA solution for this too, which is quite welcome. But please resolve the above dilemma too.
- SergeiBaklan that was a good question I should have asked.
KanwalNo1 you can just leave that row parameter blank:
=INDEX($A$5#,,1)
to return all the rows and only 1st columnmtarler So in the attached I tried just using SUMIFS and it worked fine. The problem you were having is that the columns of data you were comparing were different lengths so inside the SUMPRODUCT it would index each array (which is also not what you wanted) and after the shorter array ended it would create the error.
Here is the formula I used:
= SUMIFS(BDBL337FinlPs,BDBL339BPIDS,INDEX($A$5#,,1))see attached
- SergeiBaklan
mtarler
Aaaaaah !
It seems my fascination with SUMPRODUCT is hindering my vision. Thanks a Lot Mtarler !
You people have re-ignited my Love for this Community ! Thanks for being there.......
BTW just out of curiosity, is there a way to make SUMPRODUCT achieve this 🙂
-----------Old Habits die Hard-----------
- mtarler
=INDEX($A$5#,,1) does work if used as it is in a cell. It will return full range. But as per my understanding, if we need to make it work like a dynamic array formula, when wrapped within a non-dynamic array function, then SEQUENCE function make that happen.
I have experienced that in past (or it solved my problem, so I might have believed it to be like that). So whenever I need to force a Non-Dynamic Array Function to return results for an entire range, using sequence within the INDEX to return the desired value for each row solved my issue.
