Using SUMIFS Between Multiple Values

I

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32
Hello, i'm looking for a cleaner way to use SUMIFS for multiple Tier structures.

My formula in the table is =SUMIFS(Table2[Quantity Sold],Table2[Quantity Sold],"<="&B2,Table2[Quantity Sold],">"&B1)

For Quantity sold, i am using =RANDBETWEEN(1,100000) just to fill the range of my dummy Tiers.

One of my issues is that in the formula in the 1st row, is referencing the header row. The other issue is that i am not ensuring that the value is between two Tiers, only less than the current row, and greater than the row before it.

[TABLE="width: 293"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Tier[/TD]
[TD]Quantity Sold[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]10241[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]82869[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]19288[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]34180[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]83812[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]58339[/TD]
[TD]42272[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]9346[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]41955[/TD]
[TD]29529[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]30,000[/TD]
[TD="align: right"]3397[/TD]
[TD]24364[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]24364[/TD]
[TD]76135[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]87525[/TD]
[TD]612345[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]69222[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]61739[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]96120[/TD]
[TD]3397[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]8,000[/TD]
[TD="align: right"]72719[/TD]
[TD]0

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I realized my example may be a bit confusing, or circular.

Basically, my real data set would more look like below with Purchase order #.

On another sheet i have the possible Tier structures for each Fruit . Apples have their own Tier pricing range, Oranges have another etc.

I'm looking for a SUMIFS formula or otherwise, to sum the Quanity sold when it fits within the Tier range columns in my 1st post.

[TABLE="width: 253"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]PO[/TD]
[TD]Quantity Sold[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]98765432[/TD]
[TD="align: right"]43790[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]98765433[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]98765434[/TD]
[TD="align: right"]57340[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]98765435[/TD]
[TD="align: right"]22765[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]98765436[/TD]
[TD="align: right"]79466[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]98765437[/TD]
[TD="align: right"]33735[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]98765438[/TD]
[TD="align: right"]96753[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]98765439[/TD]
[TD="align: right"]7352[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]98765440[/TD]
[TD="align: right"]49946[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]98765441[/TD]
[TD="align: right"]9256[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD="align: right"]98765442[/TD]
[TD="align: right"]82832[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]98765443[/TD]
[TD="align: right"]42758[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]98765444[/TD]
[TD="align: right"]67481[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]98765445[/TD]
[TD="align: right"]60450[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]98765446[/TD]
[TD="align: right"]76524[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
My work-list is over 100 unique items, each having multiple pricing Tiers.

Code: 
[TABLE="width: 198"]
 <colgroup><col width="42" style="width:32pt"> <col width="64" style="width:48pt"> <col width="92" style="width:69pt"> </colgroup><tbody>[TR]
  [TD="class: xl74, width: 42"]Tier[/TD]
  [TD="class: xl74, width: 64"]Material[/TD]
  [TD="class: xl74, width: 92"]Ord.Qty[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]150[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]13275[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]225[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]6020[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]300[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]20114[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]375[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]12311[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]450[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]1623[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]750[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]14650[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]18744[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]2250[/TD]
  [TD="class: xl75, align: right"]58258123[/TD]
  [TD="class: xl75, align: right"]15240[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]150[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]12675[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]225[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]24180[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]300[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]13526[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]375[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]23276[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]450[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]4820[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]750[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]7681[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]74006124[/TD]
  [TD="class: xl75, align: right"]19740[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]150[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]17095[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]225[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]17015[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]300[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]1301[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]375[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]16531[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]450[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]10319[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]750[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]13303[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]74072125[/TD]
  [TD="class: xl75, align: right"]16618[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]21275[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]7500[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]24014[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]15000[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]12049[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]22500[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]1396[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]30000[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]4390[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]45000[/TD]
  [TD="class: xl75, align: right"]91470126[/TD]
  [TD="class: xl75, align: right"]20588[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]150[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]24245[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]225[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]20017[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]300[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]1128[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]375[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]3571[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]450[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]1076[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]750[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]12207[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]63858127[/TD]
  [TD="class: xl75, align: right"]5039[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]150[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]20158[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]225[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]13733[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]300[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]6587[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]375[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]2145[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]450[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]8782[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]750[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]19745[/TD]
 [/TR]
 [TR]
  [TD="class: xl75, align: right"]1500[/TD]
  [TD="class: xl75, align: right"]76093128[/TD]
  [TD="class: xl75, align: right"]16296[/TD]
[/TR]
</tbody>[/TABLE]

A formula i have been working on, which is inaccurate for about 5% of the Tiers, look confusing like this: Any ideas? :)

=IF($B2=150,SUMIFS(Table5[Ord.Qty],Table5[Ord.Qty],"<"&$B3,Table5[Ord.Qty],"<="&$B3,Table5[Material],$C2,Table5[Date Formula],J$1),IF($B2>=$B3,SUMIFS(Table5[Ord.Qty],Table5[Ord.Qty],">="&$B2,Table5[Material],$C2,Table5[Date Formula],J$1),SUMIFS(Table5[Ord.Qty],Table5[Ord.Qty],">="&$B2,Table5[Ord.Qty],"<"&$B3,Table5[Material],$C2,Table5[Date Formula],J$1)))
 
Upvote 0
Hi again. Going to try giving this another bump. Does anyone know if my approach is even right? Am i looking at the problem too insularly?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top