# SUMIF With Or Criteria

#### Tom Allen

##### Board Regular
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:
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Special-K99

##### Well-known Member
Maybe

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

#### Tom Allen

##### Board Regular
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

##### MrExcel MVP, Moderator
=SUMPRODUCT(SUMIF(A3:A100,A4:B4,K3:K100))

should work.

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
Brilliant thanks for the assistance guys.

Replies
4
Views
78
Replies
14
Views
345
Replies
14
Views
208
Replies
2
Views
59
Replies
2
Views
76