 # Sumif

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?

2 Replies

# Re: Sumif

=SUMPRODUCT(((A2:A16="A")+(A2:A16="E"))*(B2:B16="North")*C2:C16)

# Re: Sumif

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.