Oct 23 2021 06:51 AM
I have three columns:
1-Type
2-Work
3-Value
I would like to sum column 3 with one condition from column 2 and multiple conditions from column 1.
How would I do this?
Oct 23 2021 06:59 AM
=SUMPRODUCT(((A2:A16="A")+(A2:A16="E"))*(B2:B16="North")*C2:C16)
Please see attached worksheet.
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.