SUMIF With Or Criteria

[Tom Allen]

Hi I am trying to get a SUMIF formula to work with an Or criteria, but I cant get it to work if the criteria references a cell instead of a static value.

Here is my current code:

=SUM(SUMIF(A3:A100,{"Apples","Oranges"},K3:K100))

This code works if either Apples or Oranges is found in any cell between the range A3:A100.

However what I am trying to achieve is I want to change Apples to A4 and Oranges to B4, so that the criteria picks up what ever is in cell A4 or B4.

I have tried various things but i cant seem to get it to work when the criteria is one of two cells.

I would be grateful for any assistance.

 

[Special-K99]

Maybe

=SUMPRODUCT((A3:A100=A4)+(A3:A100=B4)*(K3:K100))

 

[Tom Allen]

Thanks for the reply, it works for the B4 criteria but when it finds the criteria in A4 it only ever adds 1 instead of the actual value in column 4.

 

[RoryA]

=SUMPRODUCT(SUMIF(A3:A100,A4:B4,K3:K100))

should work.

 

[RoryA]

Maybe

=SUMPRODUCT((A3:A100=A4)+(A3:A100=B4)*(K3:K100))

You need some more parens:
=SUMPRODUCT(((A3:A100=A4)+(A3:A100=B4))*(K3:K100))

 

[Tom Allen]

Brilliant thanks for the assistance guys.