Conditional Summing Problem

Hey All,

Thank you in advance for your time. I'm new to this forum (actually, I've never posted to an excel forum so forgive me if I'm doing something incorrectly). I'm pretty familiar with excel to a degree but lack some advanced knowledge. I work in supply chain management which entails building plans for each shift then overseeing the execution of that plan. I'm currently trying to build a tool that helps me be more accurate with carton flow by period throughout a 10 hour shift. I've attached a document that replicates what I'm trying to solve for but here is a quick overview of that challenge I'm facing:

Problem: Be able to calculate total cartons by period based on a pull sequence. For example, this plan has us pulling workstation A first, then B, C, D, E, and finally F. In period 1 we pull 315 pallets (175 pallets from workstation A, 140 pallets from workstation B). In period 2 we pull 270 pallets (the remaining pallets (60) from workstation B, all of workstation C (125) and D (50), and 35 pallets from workstation E). Period 3 we pull the remaining 65 pallets from workstation E and 160 pallets from the last workstation F.

Desired Outcome: I'm currently doing the math manually for each period which can be time consuming. I was looking for a formula in cells range F12:F14 that could do the math for me. I'm thinking it may be a SUMIF/SUMIFS function but what I've tried hasn't worked thus far. Again, any help will be much appreciated!

Are you able to show the math for what you would expect to be the end result?

Thank you for the quick responses everyone!

@kersplash - The math I usually do would do is take the total pallets worked in a period and subtract pallets starting with this first workstation. If we aren't able to complete a workstation I'll take what we will pull and multiply that by the cartons/pallet. Using the example from the worksheet, the first period we will pull 315 pallets which will be all of workstation 1 (175 pallets for 3000 cartons), and 140 pallets of workstation 2: 140*7.5=1050. I would take that number and add it to the 3000 cartons from the first workstation which will have us pulling 4050 cartons in the first period. This may not seem like much of a workload but sequences, staffing, and pallets/hr can change frequently throughout the shift.

@bebo021999 - Is there referent table you made dynamic? For instance if the next day we have a backlog of say, 2000 pallets for 20000 cartons spread across all workstations, and we decide to start in workstation D instead of E would the table you created still reflect accurately? If so then this would work!

@kvsrinivasamurthy - Macros are welcomed! The current workbook I have now has quite a few that pull from external sources to create the backlog. I would send it but the information that it uses is proprietary and requires a login.

Again thank you for the help. This has stumped me for weeks!