Conditional Summing Problem

**metz08** · 01-23-2018, 12:23 AM

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!

**kersplash** · 01-23-2018, 12:36 AM

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

**bebo021999** · 01-23-2018, 01:04 AM

I use referent table as helper . See attachment

**kvsrinivasamurthy** · 01-23-2018, 04:02 AM

Macro solution is better choice. Is it ok.

**metz08** · 01-24-2018, 01:43 AM

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!

**bebo021999** · 01-26-2018, 11:59 AM

Originally Posted by metz08

@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!

The referent table is dynamic, I set up to 1691 pallets and you can copy down for more pallets. It is following the order 1,2,...,6 in column E, sequence,

**kvsrinivasamurthy** · 01-24-2018, 11:03 PM

I pl upload file with more details and data

**kvsrinivasamurthy** · 01-30-2018, 04:30 AM

Here is the Codefor macro. Change the ranges as required.

Sub GetCartoons()

Dim PeriodPeletRng As Range, PeletRng As Range, cel1 As Range, cel2 As Range
Dim Sh As Worksheet
Dim CartonNos As Long, RemPelet As Long, X As Long, Y As Long, K As Long
Dim CartAry()

Set Sh = ActiveSheet
Set PeletRng = Sh.Range("B2:B7")
Set PeriodPeletRng = Sh.Range("E12:E14")

CartAry = WorksheetFunction.Transpose(WorksheetFunction.Transpose(PeletRng))

For Each cel1 In PeriodPeletRng
    
    For Each cel2 In PeletRng
    Y = Y + 1
    If CartAry(Y, 1) > 0 Then
    
    If RemPelet > 0 Then K = RemPelet Else K = cel1
    
    CartonNos = CartonNos + WorksheetFunction.Ceiling(WorksheetFunction.Min(K, CartAry(Y, 1)) * cel2.Offset(0, 2).Value, 1)
        
        If K > CartAry(Y, 1) Then
        RemPelet = K - CartAry(Y, 1)
        CartAry(Y, 1) = 0
        X = X + 1
        Else
        CartAry(Y, 1) = CartAry(Y, 1) - K
        Exit For
        End If
        
    End If
    
    Next cel2
    
cel1.Offset(0, 1) = CartonNos
CartonNos = 0
RemPelet = 0
Y = 0

Next cel1

End Sub

Conditional Summing Problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Re: Conditional Summing Problem

Thread Information

Users Browsing this Thread

Similar Threads

An other summing problem

VBA Summing Problem

Summing problem

Problem with conditional summing

Problem in summing up a row

summing problem!!!!!!

problem with summing

conditional Summing

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions