Occasional Visitor

I have three columns:






I would like to sum column 3 with one condition from column 2 and multiple conditions from column 1.


How would I do this?

2 Replies



Please see attached worksheet.


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,
            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,
            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.