Jul 22 2021 10:18 PM - edited Jul 22 2021 10:22 PM
I have a large data set (1000+ rows with 100+ columns)
Below screen shot is a scaled-down version of the data set but I think it serves a good example.
There is where my problem starts. I am left with a filtered table that has multiple values per year.
And i want to add them up for ever year (instead of getting 2 values for 2021, I want to add them to a single value).
This seems do-able but I am not sure how. Please help
Jul 22 2021 11:51 PM
Hi @papa_austin
Thanks for providing a sample + expected result. Decomposed this could be something like:
=LET(
h, (Table1[#Headers]=B24) + (Table1[#Headers]=C24) + (Table1[#Headers]="Year"),
f, FILTER( FILTER(Table1,Table1[Text1]=B23), h),
y, INDEX(f,,1),
s, SEQUENCE(,COLUMNS(f)-1,2),
v, INDEX(f,SEQUENCE(ROWS(f)),s),
t, MMULT(v, SEQUENCE(COLUMNS(v))^0),
CHOOSE({1;2}, y, t)
)
This is implemented in the attached file
I would highly recommend you refer to Excel 365 SUM dynamic array at row level where you'll find different approaches to this and other calcs
Jul 23 2021 12:03 AM
If I understand correctly, I think this is one way you could do it.
=SUM(IFERROR(NUMBERVALUE(Table1),0)*MMULT((Table1[Year]=A38)*(Table1[Text1]=$B$23:$C$23),--(TRANSPOSE(COLUMN($B$23:$C$23)>0)))*TRANSPOSE(MMULT(--(TRANSPOSE(Table1[#Headers])=$B$24:$C$24),--(TRANSPOSE(COLUMN($B$24:$C$24)>0)))))
Jul 23 2021 02:35 AM
Jul 23 2021 03:39 AM
With an Helper sheet (can be hidden) then. See attached file where I named your 3 inputs/parameters + the dynamic arrays that sit in the helper sheet