Sum numbers in one column until the sum of numbers in another column reaches a certain amount

[xl_junkie]

Hello,

I have a worksheet containing monthly well production data. There are columns for the well number, month (date), barrels produced that month, and number of days the well was actively producing that month.

I am trying to write a formula that will give the total number of barrels produced for a given well over the first 240 days of production.

So basically I think I need to add the numbers in the third column until the numbers in the fourth column exceed 240, then multiply the difference between the sum of days producing up to that point and 240, and multiply this difference by the average daily production for the final month. The product of this last step would then be added to the sum of third column numbers up to that point.

Seems straightforward enough, but I am having a difficult time figuring this one out.

Here is a sample of the data:

[TABLE="width: 260"]
<tbody>[TR]
[TD]Well Number[/TD]
[TD]Date[/TD]
[TD]Barrels Produced[/TD]
[TD]Days Producing[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]7/1/1961[/TD]
[TD="align: right"]887[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]8/1/1961[/TD]
[TD="align: right"]3479[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]9/1/1961[/TD]
[TD="align: right"]3320[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]10/1/1961[/TD]
[TD="align: right"]3362[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]11/1/1961[/TD]
[TD="align: right"]3275[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]12/1/1961[/TD]
[TD="align: right"]1834[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]1/1/1962[/TD]
[TD="align: right"]1372[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]2/1/1962[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]3/1/1962[/TD]
[TD="align: right"]1706[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]4/1/1962[/TD]
[TD="align: right"]1280[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]5/1/1962[/TD]
[TD="align: right"]1525[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]6/1/1962[/TD]
[TD="align: right"]1334[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]2853[/TD]
[TD="align: right"]7/1/1962[/TD]
[TD="align: right"]2093[/TD]
[TD="align: right"]31[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[VorLag]

You could try some embedded if statements based on how many criteria you need. So something like this:

IF(SUM($D$1:D1)>=240,[ENTER EQUATION],SUM($D$1:D1))

I'm not understanding what you're doing, but this basically allows you to drag the formula down to automatically adjust the sum range. If the sum is greater than or equal to 240, you can have it appear as a blank or multiple a number by something, anything you need.

 

[Rick Rothstein]

How about a UDF (user defined function) to get the result you want? The function takes two arguments... the well number (which can be hard-coded or be a cell reference) and the number of production days you want the total for (240 for your posted question, but you can change it if you want to see the result for a different time period)...

Function Barrels(WellNumber As Variant, ProductionDays As Long) As Long
  Dim X As Long, MaxDays As Long, Days As Long
  Application.Volatile
  For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(X, "A").Value = WellNumber Then
      If Days + Cells(X, "D").Value > ProductionDays Then
        Barrels = Barrels + (Days + Cells(X, "D").Value - ProductionDays) * Cells(X - 1, "C").Value / Cells(X - 1, "D").Value
        Exit Function
      Else
        Days = Days + Cells(X, "D").Value
        Barrels = Barrels + Cells(X, "C").Value
        If Days = 240 Then Exit Function
      End If
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Barrels just like it was a built-in Excel function. For example,

=Barrels(2853,240)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[xl_junkie]

How about a UDF (user defined function) to get the result you want? The function takes two arguments... the well number (which can be hard-coded or be a cell reference) and the number of production days you want the total for (240 for your posted question, but you can change it if you want to see the result for a different time period)...

Function Barrels(WellNumber As Variant, ProductionDays As Long) As Long
  Dim X As Long, MaxDays As Long, Days As Long
  Application.Volatile
  For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(X, "A").Value = WellNumber Then
      If Days + Cells(X, "D").Value > ProductionDays Then
        Barrels = Barrels + (Days + Cells(X, "D").Value - ProductionDays) * Cells(X - 1, "C").Value / Cells(X - 1, "D").Value
        Exit Function
      Else
        Days = Days + Cells(X, "D").Value
        Barrels = Barrels + Cells(X, "C").Value
        If Days = 240 Then Exit Function
      End If
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Barrels just like it was a built-in Excel function. For example,

=Barrels(2853,240)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you Rick Rothstein, that is exactly what I needed! I figured I might need to use VB, but I wasn't sure where to start.

 

[Rick Rothstein]

Thank you Rick Rothstein, that is exactly what I needed! I figured I might need to use VB, but I wasn't sure where to start.

You are quite welcome... I am glad I was able to help. I would suggest, though, that you double check the output from my UDF against some existing known, manually calculated (and verified) historical data to make sure I got the math correct (your business model is a new one for me, so I have nothing to fall back on in deciding if my code is correct or not).