# Using Sumifs with an OR clause and cell Reference

#### ImGoing2needaFormula

##### New Member
Hey all, first time posting, though been lurking these boards for quite some time. I am in need of some assistance with a Sumifs/sumproduct that has an or statement with cell reference.
The formula which I know does not work is below.
=SUM(SUMIFS('Sheet2'!\$J:\$J,'Sheet2'!\$F:\$F,"LC",'Sheet2'!\$C:\$C,{A33,B33,C33}))
Column J on Sheet 1 has all the data I want sum'd (Dollars)
Column F I need to parse to make sure it meets the LC criteria (person)
And for the last criteria I need it to match one of the three critera (Type of product) Now these are going to change base upon the row
I had just been doing this formula =Sumifs(1)+Sumifs(2)+Sumifs(3) currently but was hoping I can cut the formula short and just have one formula.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### Well-known Member
Welcome to MrExcel Message Board.
At this situation, you should use Cells Value Not Address, Then Change A33, B33 & C33 to their values at formula.

##### Well-known Member
Or Use Sumproduct if your range is not very big. I supposed your range is row 2:90 Then:
Excel Formula:
=SUMPRODUCT((Sheet2!\$J\$2:\$J\$90)*(Sheet2!\$F\$2:\$F\$90="LC")*((Sheet2!\$C\$2:\$C\$90=A33)+(Sheet2!\$C\$2:\$C\$90=B33)+(Sheet2!\$C\$2:\$C\$90=C33)))
But if your range is Long this formula take long time to respond:
Excel Formula:
=SUMPRODUCT((Sheet2!\$J:\$J)*(Sheet2!\$F:\$F="LC")*((Sheet2!\$C:\$C=A33)+(Sheet2!\$C:\$C=B33)+(Sheet2!\$C:\$C=C33)))

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Another option
Excel Formula:
=SUM(SUMIFS('Sheet2'!\$J:\$J,'Sheet2'!\$F:\$F,"LC",'Sheet2'!\$C:\$C,A33:C33))
may need array entry in xl 2019

Replies
9
Views
308
Replies
5
Views
526
Replies
5
Views
381
Replies
8
Views
246
Replies
2
Views
146

1,136,733
Messages
5,677,445
Members
419,693
Latest member
divtjd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back