SumIfs based on Value <

gheyman

Well-known Member
I am trying to do a type of SumIfs.

My formula is going in K3 (and will be copied down to the end of the sheet)

If the Value in Column \$B:\$B matches the Value in \$B3 and the Value in \$F:\$F matches the Value in \$D3 AND the Value \$I:\$I is less than the value in \$I3

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

gheyman

Well-known Member
Instead, I can use a SumIfs like above but i need my last condition to be 1 less than

=SUMIFS(W:W,V:V,T5,S:S,S5,U:U,U5&"-1") This is just an example - it doesn't work. I am trying to sum if the value in U is 1 less than the value in U5

Jeffrey Mahoney

Well-known Member
You didn't specify the column you want to sum. How about this:

=SUMIFS(Range to sum,\$B:\$B,\$B3,\$F:\$F,\$D3,\$I:\$I,"<"&I3)

Jeffrey Mahoney

Well-known Member

=sumifs(w:w,v:v,t5,s:s,s5,u:u,u5-1)

Replies
4
Views
198
Replies
2
Views
709
Replies
7
Views
101
Replies
2
Views
341
Replies
3
Views
71

1,195,953
Messages
6,012,511
Members
441,703
Latest member
clivelincoln

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

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