I have a Dynamic Array and I want to sum the Salary Column.
The attached file is for illustration- there would be easier ways to get it without Dynamic Array but in the real solution I need the Dynamic Array approach.
It is likely that more names will be added to the SourceTable.
How does one get the total for the Salary Column.
Thank You
Allister
J10=SUM(FILTER(t_data[Salary],H5#=t_data[Name]))
I tried this but when I add more names to theSource Data the Total doesn't move down and I get a SPILL w error.
Are there any other ideas ?
Thank You
Allister
Last edited by AllisterB; 04-21-2021 at 04:40 PM.
Please try
=LET(a,t_data,b,UNIQUE(INDEX(a,,1)),c,IF(SEQUENCE(ROWS(b)+1)>ROWS(b),"Total",b),CHOOSE({1,2},c,SUMIFS(INDEX(a,,2),INDEX(a,,1),SUBSTITUTE(c,"Total","*"))))
Thank you
This does work in your workbook. However theCheck Column is not part of your solution. This Coloumn is representative of other Columns in the real solution that are calculations using other values on the row.. the solution I need must allow for such columns
So again thank you for your efforts but its not quite what I need .
Kind regards
Allister
However
Maybe this?
Formula:
Please Login or Register to view this content.
Dave
Thanks Dave - but again when t-Data expands theformula to Sum stays whereit is on teh sheet and I get a #Spill Error.
Try in H5 and fill down.Then in J5 and fill down.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Last try
=LET(a,t_data,b,UNIQUE(INDEX(a,,1)),c,IF(SEQUENCE(ROWS(b)+1)>ROWS(b),"Total",b),CHOOSE({1,2,3},c,"",SUMIFS(INDEX(a,,2),INDEX(a,,1),SUBSTITUTE(c,"Total","*"))))
aa.png
Nice Solution - I think this solution might just do it.
Thanks Bo_Ry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks