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 calculate...
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.
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".