Sumif Non Continuous Range - Allocation

[phlemons]

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!

 

[jasonb75]

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]

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]

Try changing the SUMIF part to

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

 

[phlemons]

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!