How to sum a row when the sum of a different row equals a certain value, and only between two dates

[drmcbiz]

Hi all,

Sorry for the confusing title. Having problems figuring this out elegantly. I've got a huge workbook so I was hoping to solve this in one cell (as I need to solve this thousands of times in the one workbook and having multiple rows of formula to figure out the answer keeps making my Excel crash!).

What I'm trying to do is this:

Between the dates of 2/1/23 and 9/1/23 I want to sum up the profit made (column D) on the first $500 of notes exercised (column C). The answer here should be $20 (highlighted in green).

Thanks!

 

[drmcbiz]

Welcome to the MrExcel board!

Will this number always occur exactly by summing column B from the start date?
For example, with the sample data from post 31, could you be looking to sum the profits on, say, the first $350 of notes exercised?

Correct - both the amount of notes to be summed and the dates to sum between will change. I might just want to sum with first $200 of notes exercised between 2/1 and the 4/1 for example.

 

[Peter_SSs]

Correct - both the amount of notes to be summed and the dates to sum between will change. I might just want to sum with first $200 of notes exercised between 2/1 and the 4/1 for example.

But this example ($200) still is an exact amount of 2 rows at $100. My example was $350. If that is possible then by my understanding the suggestion in post #7 does not produce the correct result.
Here it is below. Shouldn't the result in that case be 14 not 9 since you would nee to add rows 109:112 to get up to (& past) $350 in column B?

drmcbiz.xlsm
	A	B	C	D
108	1/01/2023	100	1	0
109	2/01/2023	100	2	100
110	3/01/2023	100	3	200
111	4/01/2023	100	4	300
112	5/01/2023	100	5	400
113	6/01/2023	100	6	500
114	7/01/2023	100	7	600
115	8/01/2023	100	8	700
116	9/01/2023	100	9	800
117		TOTAL	9
118
119	Limit	350
120	Start Date	2/01/2023
121	End Date	9/01/2023
Sheet2

Cell Formulas
Range	Formula
D108	D108	=IF(AND(A108>=B$120,A108<=B$121),B$108,0)
D109:D116	D109	=IF(AND(A109>=B$120,A109<=B$121),SUM(D108,B109),0)
C117	C117	=SUMIFS(C108:C116,A108:A116,">="&B120,A108:A116,"<="&B121,D108:D116,"<="&B$119)

 

[drmcbiz]

Hi Peter,

Sorry, yes you're right there.

I'm trying to obtain that answer (14) using once cell (without the addition of column D)

 

[Peter_SSs]

I don't think that you will like this any better than using helper cells. It is particularly long since you are using an old version of Excel.

drmcbiz.xlsm
	A	B	C
1
2
3	1/01/2023	100	1
4	2/01/2023	100	2
5	3/01/2023	100	3
6	4/01/2023	100	4
7	5/01/2023	100	5
8	6/01/2023	100	6
9	7/01/2023	100	7
10	8/01/2023	100	8
11	9/01/2023	100	9
12
13
14	Start	2/01/2023
15	End	9/01/2023
16	First $	500
17	Sum	20
Sheet1

Cell Formulas
Range	Formula
B17	B17	=SUM(INDEX(C3:C11,MATCH(B14,A3:A11,0)):INDEX(C3:C11,MATCH(B14,A3:A11,0)+MATCH(B16,SUMIF(INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),"<="&INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),INDEX($B$3:$B$11,MATCH(B14,A3:A11,0)):INDEX(B3:B11,MATCH(B15,A3:A11,0))))+ISNA(MATCH(B16,SUMIF(INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),"<="&INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),INDEX($B$3:$B$11,MATCH(B14,A3:A11,0)):INDEX(B3:B11,MATCH(B15,A3:A11,0))),0))-1))

drmcbiz.xlsm
	A	B	C
1
2
3	1/01/2023	100	1
4	2/01/2023	100	2
5	3/01/2023	100	3
6	4/01/2023	100	4
7	5/01/2023	100	5
8	6/01/2023	100	6
9	7/01/2023	100	7
10	8/01/2023	100	8
11	9/01/2023	100	9
12
13
14	Start	2/01/2023
15	End	9/01/2023
16	First $	350
17	Sum	14
Sheet1

Cell Formulas
Range	Formula
B17	B17	=SUM(INDEX(C3:C11,MATCH(B14,A3:A11,0)):INDEX(C3:C11,MATCH(B14,A3:A11,0)+MATCH(B16,SUMIF(INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),"<="&INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),INDEX($B$3:$B$11,MATCH(B14,A3:A11,0)):INDEX(B3:B11,MATCH(B15,A3:A11,0))))+ISNA(MATCH(B16,SUMIF(INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),"<="&INDEX($A$3:$A$11,MATCH(B14,A3:A11,0)):INDEX(A3:A11,MATCH(B15,A3:A11,0)),INDEX($B$3:$B$11,MATCH(B14,A3:A11,0)):INDEX(B3:B11,MATCH(B15,A3:A11,0))),0))-1))

 

[Peter_SSs]

You could compare this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the mini sheet.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

At this stage I have assumed that there will be sufficient rows with suitable dates to achieve the dollar amount of notes exercised.
If it is possible that there is not enough rows in the date range to achieve the dollar amount of notes exercised (eg with the same dates below and the value in row 16 was, say, 2000) what should happen?

Also, if it is possible that the start date is later than the latest date in column A, what should happen?

Function ProfitSum(rng As Range, dStart As Date, NotesExercised As Double) As Double
  Dim a As Variant
  Dim i As Long, j As Long
  Dim NotesSum As Double
 
  a = rng.Value
  i = 1
  Do Until a(i, 1) >= dStart
    i = i + 1
  Loop
  Do Until NotesSum >= NotesExercised
    NotesSum = NotesSum + a(i + j, 2)
    ProfitSum = ProfitSum + a(i + j, 3)
    j = j + 1
  Loop
End Function

drmcbiz.xlsm
	A	B	C
1
2
3	1/01/2023	100	1
4	2/01/2023	100	2
5	3/01/2023	100	3
6	4/01/2023	100	4
7	5/01/2023	100	5
8	6/01/2023	100	6
9	7/01/2023	100	7
10	8/01/2023	100	8
11	9/01/2023	100	9
12
13
14	Start	2/01/2023
15	End	9/01/2023
16	First $	500	350
17	Sum	20	14
Sheet1

Cell Formulas
Range	Formula
B17:C17	B17	=ProfitSum($A$3:$C$11,$B$14,B16)

 

[Bosquedeguate]

Thanks for the answers so far everyone, much appreciated.

Post #7 from Bosquedeguate gets the answer I'm after by adding the extra column. However, if I add this column to my workbook it will increase its size many fold and the system crashes.

Is it possible to solve within 1 cell without adding the extra column?

I have only basic VBA knowledge, but the inputs (note amount and profit) are dynamic and hence I imagine VBA would not update these continuously / be appropriate?

Thanks

The VBA function is like any standard Excel function. … when you change a value the is a function argument it recalculates the cell(s) where you have it.

Also, if the $500 sum limit will not change that can be a constant in the VBA code, and eliminated as an argument. Or … if the Limit, Start Date, and End Date will always be in fixed cells - These arguments to the function can be removed. The code would always grab the 3 values from a fixed cells on the sheet containing the values.
Just need your clarification and direction on this.

 

[Bosquedeguate]

The VBA function is like any standard Excel function. … when you change a value the is a function argument it recalculates the cell(s) where you have it.

Also, if the $500 sum limit will not change that can be a constant in the VBA code, and eliminated as an argument. Or … if the Limit, Start Date, and End Date will always be in fixed cells - These arguments to the function can be removed. The code would always grab the 3 values from a fixed cells on the sheet containing the values.
Just need your clarification and direction on this.

Again a VBA function eliminates the need for an extra column in the Notes table. Adding VBA code to you Excel file is easy, but it does require saving the file as a Macro-enabled file.

 

[drmcbiz]

Amazing, thank-you all for your help. Peter_SSs formula has solved it in a way which works for my sheet.

 

[Peter_SSs]

Peter_SSs formula has solved it in a way which works for my sheet.

Glad it worked for you. Thanks for letting us know.

I'm a bit surprised though that given the comment below that huge formula evaluated so many times doesn't bog your sheet down.

.. as I need to solve this thousands of times in the one workbook

 

[drmcbiz]

Hi Peter,

It certainly slows my sheet down (takes about 30 seconds to update each time I change a variable). However, I was able to get the answer i needed (where adding the extra column crashed the sheet entirely!)