Sumif

Occasional Visitor

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

@pareshtsaraplast 

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

Please see attached worksheet.

@pareshtsaraplast 

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.