Forum Discussion
Lambda for Getting Sums Based on Dynamic Filters
A while back, someone helped me by drafting a Lambda formula that counted the number of rows in a dynamic table based on multiple dynamic filter values. I now have a need to modify it so it calculates the sums of a column in a table based on the same premise. This is the lambda formula for if it were to be applied to the same spreadsheet:
=LAMBDA(tblall,BeginDate,EndDate,Dept,PRAM,LET(tbl,DROP(tblall,1),hdrs,TAKE(tblall,1),nofltr,SEQUENCE(ROWS(tbl)),result,FILTER(nofltr,(INDEX(tbl,,MATCH("Date",hdrs,0))>=Begin)*(INDEX(tbl,,MATCH("Date",hdrs,0))<=EndDate)*(IF(Dept="",nofltr,INDEX(tbl,,MATCH("Staff & Agencies Involved",hdrs,0))=Dept))*(IF(PRAM="",nofltr,INDEX(tbl,,MATCH("PRAM #",hdrs,0))=PRAM)),"None Found"),COUNT(result)))
My initial thought is that the change would take place in part where it defines "nofltr" as the number of rows [.....nofltr,SEQUENCE(ROWS(tbl)),.....] and where it filters the defined "result" [...COUNT(result)....].
I can't quite figure out what needs to be modified. I've used some very convoluted IF functions in the past to do the same thing, but this has proven to be a lot neater and more scalable for large numbers of dynamic filters.
8 Replies
Why not simple FILTER() then SUM()? Can you attach a sample file and show your desired output so that we can simplify the formula for you if possible.
Sorry for the delayed reply. What complicates this is the Excel workbook works as a dashboard where data is brought in by PowerAutomate. There are a lot of dynamic pieces, which in other dashboards made it really difficult to utilize just the FILTER() function. I could just have a limited understanding of the function, though. Attached is a sample file.
With respect to the author of the Lambda, I kept most of it intact but changed MATCH to XMATCH where needed and added a sum_array parameter so you can tell it which column to total.
SumFilteredλ = LAMBDA(tblall, sum_array, BeginDate, EndDate, Dept, PRAM, LET( tbl, DROP(tblall, 1), hdrs, TAKE(tblall, 1), nofltr, SEQUENCE(ROWS(tbl)), result, FILTER( sum_array, (INDEX(tbl, , XMATCH("Date", hdrs)) >= BeginDate) * (INDEX(tbl, , XMATCH("Date", hdrs)) <= EndDate) * (IF(Dept = "", nofltr, INDEX(tbl, , XMATCH("Staff & Agencies Involved", hdrs)) = Dept)) * (IF(PRAM = "", nofltr, INDEX(tbl, , XMATCH("PRAM #", hdrs)) = PRAM)), 0 ), SUM(result) ) )It's important to note the author wrote the function with tables (including header) or ranges (including header) in mind.
For this part: (IF(Dept = "", nofltr, INDEX(tbl, , XMATCH("Staff & Agencies Involved", hdrs)) = Dept))
One thing I forgot to mention is that the Dept value would need to be formatted for a partial text match. The context is that the data in the "Staff & Agencies Involved" column contains answers from a multi-selection choice question in MS Forms, which comes out looking something like ["Option1","Option2","Option3"]. My understanding is that FILTER() doesn't support wildcards, but some other online sources suggested something like this might work:
.....*(IF(Dept="",noflter,INDEX(tbl,,XMATCH("Staff & Agencies Involved",hdrs))=(ISNUMBER(SEARCH(Dept,Table1[Staff & Agencies Involved]))*.....This doesn't appear to work, though. Any ideas? Your solution works great otherwise.
If wildcards are needed I'm inclined to use regex. One thing that could be simplified is removing the need to find the column where the term(s) might be located. If you're confident of the column order then the function can be simplified.
This works for all except one part. Dept needs to be able to search by a partial text match. Normally I'd just use "*Dept*" or "*(Dept)*", but I can't seem to make excel recognize it as a partial text match for the defined value rather than the word "Dept".
This worked like a charm. Thank you so much!
