Using Sumifs with an OR clause and cell Reference

Joined
May 5, 2021
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,924
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,136
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

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?

Disable AdBlock

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
Top