# Sumif Non Continuous Range - Allocation

#### phlemons

##### New Member
First time poster and hoping someone might have a solution to a sumif allocation problem I am having.

I am putting together a retail allocation tool and currently have a formula that allocates to stores based on rank and a qty available to allocate to those stores. I've included a screenshot of my current excel sheet with the formula being used. In this example, I'm looking at an item that I am going to allocate to these filtered stores in columb "G" based on their need in column "R" and their rank in column "V". The formula is currently setup to prioritize higher ranking stores to insure allocation first before lower ranked doors. You can see this in row 206 where the store gets 0 units due being a lower ranked door and not having enough units available for allocation to cover their needs.

My problem is, this formula works perfectly for this specific example, but I need to be able to drag the formula down over thousands of lines. In doing this, the qty available to allocate changes as you begin to look at different items and qty's available for allocation. I need find a way to have the sumif sum_range to look at specific cells rather than a range all together. The other problem is I need to be able to drag the formula down and have the formula find the specific cells across thousands of lines. Is there a helper cell that I could maybe use? Tried doing a vlookup within the sum_range but gives me an error.

Thanks for any help or feedback provided!

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### jasonb75

##### Well-known Member
How are you identifying the 'specific' cells?

I expect that you just need to use SUMIFS instead of SUMIF, or if you need to take the filter into consideration, then sum a helper column that is filled with =SUBTOTAL(9,R7) which will zero out any hidden rows.

#### phlemons

##### New Member
How are you identifying the 'specific' cells?

I expect that you just need to use SUMIFS instead of SUMIF, or if you need to take the filter into consideration, then sum a helper column that is filled with =SUBTOTAL(9,R7) which will zero out any hidden rows.

I'm trying to identify based off the concat in column A. This example is a jersey, color navy, and size 2xl. But that will change when I look at different items or if I'm just looking at different sizes within this same item. So the sum_range for this size are the 22 cells currently filtered but will change to a different 22 cells if I were to look at size XL rather than the currently filted 2XL.

Hopefully that makes sense. Sorry I know it is kind of complicated.

Thanks!

#### jasonb75

##### Well-known Member
Try changing the SUMIF part to

SUMIFS(S:S,V:V,"<"&V137,A:A,A137)

#### phlemons

##### New Member
Wow! That completely fixed my problem! Really appreciate your help on this Jason! I've been stuck on this formula for weeks lol Can't tell you how relieved I am to have it work!

Thanks again!

Replies
2
Views
329
Replies
1
Views
121
Replies
1
Views
233
Replies
1
Views
136
Replies
9
Views
767

1,133,428
Messages
5,658,737
Members
418,467
Latest member
sc356448

### 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.

### Which adblocker are you using?

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

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