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 worked like a charm. Thank you so much!