Forum Discussion
Sumproduct and New Dynamic Array Formula
I am using the following formula to Compute SUMPRODUCT and similarily auto-populating the formula to cover the entire range instead of entering the formula in all the cells. But it seems I have hit a bottleneck.
= SUMPRODUCT((BankPymtIDColumn=$A5)*(Amount<>"")*(Amount))
is working fine, but it needs to entered manually in all the range and future addition to those ranges.
= SUMPRODUCT((BankPymtIDColumn=INDEX($A$5#,SEQUENCE(ROWS($A$5#),1,1,1),1))*(Amount<>"")*(Amount))
is not working, though INDEX($A$5#,SEQUENCE(ROWS($A$5#),1,1,1),1) is producing the correct range. I am not sure why the same is returning only the first value instead of returning and evaluating the whole range.
9 Replies
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 column
- I believe your problem here is that you are using a SUM operation with an array that you really was to only do a partial sum but excel doesn't know how to sum some of it and spill the rest. In any case you should try using LAMBDA function and in particular the BYROWS() helper function to the LAMBDA(). I'm really unsure what you are doing but something like this might be what you want:
=BYROW($A$5#, LAMBDA(r, SUMIFS(Amount, BankPymtIDColumn, r, Amount, "<> ") ) )
note I used SUMIFS instead of SUMPRODUCT only because I think that is more what you were trying to do. You should be able to use SUMPRODUCT just the same.- Thanks Mtarler !
Let me try that. If not I will try to upload a file.
