Forum Discussion
Lambda for Getting Sums Based on Dynamic Filters
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!
I assume same result could be achieve using SUMIFS() or FILTER()/SUM() functions.
