SUMIF With Or Criteria

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92
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.
 

Some videos you may like

Excel Facts

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

Tom Allen

Board Regular
Joined
Sep 26, 2014
Messages
92
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
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
=SUMPRODUCT(SUMIF(A3:A100,A4:B4,K3:K100))

should work.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,466
Members
409,883
Latest member
asharris90
Top