Forum Discussion
Is it possible to nest filter inside of sumif?
- Office 365 without Lamda functionality
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.
- I have multiple dynamic filtering need; my real example has 3 columns that could be filtered on
- User can select what columns they want to see dynamically - up to 5 columns
- Using Filter function, I can apply the right filters and pick the columns that I need to return
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
4 Replies
- JMB17Bronze Contributor
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)))))
- LorenzoSilver Contributor
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
- papa_austinCopper ContributorHello Lorenzo
I do not have a version of Office 65 with Let function and I have no option to get it (Semi annual enterprise channel license)
While this approach would work great if I did have let/lambda features, could you do it without them?- LorenzoSilver Contributor
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