Sumif Non Continuous Range - Allocation

phlemons

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

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. 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
  1. 2016
Platform
  1. 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
12,959
Office Version
  1. 365
Platform
  1. 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
  1. 2016
Platform
  1. 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,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.
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
Top