Sumif Non Continuous Range - Allocation

phlemons

New Member
Joined
Aug 2, 2020
Messages
3
Office Version
2016
Platform
Windows
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!
Capture.JPG
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,984
Office Version
2019
Platform
Windows
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
Joined
Aug 2, 2020
Messages
3
Office Version
2016
Platform
Windows
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
Joined
Dec 30, 2008
Messages
9,984
Office Version
2019
Platform
Windows
Try changing the SUMIF part to

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

phlemons

New Member
Joined
Aug 2, 2020
Messages
3
Office Version
2016
Platform
Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,361
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top