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.

FruitTierQuantity SoldFormula
Apples25102410
Apples50828690
Apples75192880
Apples100341800
Apples500838120
Oranges20,0005833942272
Grapes10,00093460
Grapes20,0004195529529
Grapes30,000339724364
Grapes50,0002436476135
Grapes100,00087525612345
Oranges1,000692220
Oranges3,000617390
Oranges5,000961203397
Oranges8,000727190


<tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.

FruitPOQuantity Sold
Apples9876543243790
Apples9876543340
Apples9876543457340
Apples9876543522765
Apples9876543679466
Oranges9876543733735
Grapes9876543896753
Grapes987654397352
Grapes9876544049946
Grapes987654419256
Grapes9876544282832
Oranges9876544342758
Oranges9876544467481
Oranges9876544560450
Oranges9876544676524

<tbody>
</tbody>
 
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,214,621
Messages
6,120,568
Members
448,972
Latest member
Shantanu2024

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