Oct 23 2021 06:51 AM
Oct 23 2021 11:33 AM
Since you already have a correct solution, here is a bit of experimentation with 365 insider beta.
Using traditional Excel one might apply the logic row by row using a helper range. In 365, this is made possible by the MAP helper function which is capable or running through the the elements of each column.
= LET(selected, MAP( Type, Work, LAMBDA(t,w, AND(OR(t=types), w=region))), selectedValues, FILTER(Value, selected, 0), SUM(selectedValues) )
An alternative helper function is BYROW that will pick out rows of the table as range references. These may be intersected with column ranges to give individual values for the formula
= LET(selected, BYROW( table, LAMBDA(row, AND(OR((row Type)=types), (row Work)=region))), selectedValues, FILTER(Value, selected, 0), SUM(selectedValues) )
Sooner or later this is going to become instinctive but, right now, it is a voyage of discovery.