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!

 

[Bosquedeguate]

=SUMIFS(C2:C10,B2:B10,">="&B13,B2:B10,”>="&B14)

 

[Joe4]

=SUMIFS(C2:C10,B2:B10,">="&B13,B2:B10,”>="&B14)

That doesn't appear to work at all for me.
What does it return for you?

This part:

”>="&B14

probably need to change to this:

"<="&B14

There are two criteria that need to be applied - date criteria on the first column, but also dollar amount on the second column:

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).

 

[Bosquedeguate]

The criteria range should be the column for your dates. From your screen shot maybe that is column A.

Formula would be
=SUMIFS(C2:C10,A2:A10,">="&A13,A2:A10,”>="&A14)

I assuming your data is in Rows 2 to 10, and Columns A to C

 

[Bosquedeguate]

=SUMIFS(C2:C10,A2:A10,">="&A13,A2:A10,”<="&A14)

 

[Fluff]

That still doesn't take into account the 2nd criteria

on the first $500 of notes exercised (column C)

 

[Bosquedeguate]

OK ... I am thinking there are two ways to solve this problem ...

1. Add a 4th column to your data that flags it as 0 or a Running total if the date is within range
   Using 1 and 0 as your flag
   - Added Column D
   - Added a condition in the SUMIFS() parameters (see sample sheet below)
2. Or, create a VBA function to do the work. I would the following arguments: Range of the Data (3 columns); Start Date, End Date, and Running Sum Limit
   - Assumes that the format and values in columns 1,2, & 3 will not change.
   - Would not require the addition of Column D calculation (in 1 above)
   Let us know if this is viable solution for you.

Sample:

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

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)

 

[Bosquedeguate]

Here is a VBA Function that does the conditional Summing of Profit as needed.
Note this version of the code does not do any validation of the arguments it receives (to be added).

Please do some additional testing if you like this approach.

Were you expecting the function to also highlight the profit values that were used in the sum?

Using the same mini-sheet as above I used this function with the arguments below:
=SumConditional(A108:C116,B120,B121,B119)

GetLastRow.xlsm
	A	B	C
1	Date	Note Amount	Profit
2
3	1/1/2023	100	1
4	2/1/2023	100	2
5	3/1/2023	100	3
6	4/1/2023	100	4
7	5/1/2023	100	5
8	6/1/2023	100	6
9	7/1/2023	100	7
10	8/1/2023	100	8
11	9/1/2023	100	9
12		TOTAL	20
13
14	Limit	500
15	Start Date	2/1/2023
16	End Date	9/1/2023
Sheet6

Cell Formulas
Range	Formula
C12	C12	=sumconditional(A3:C11,B15,B16,B14)

Function SumConditional(rng As Range, Optional dtBeg As Date, Optional dtEnd As Date, Optional Limit)
  'Do some testing to validate argugments
  'rng 3 Columns wide
  'Column 1 of Range contain only date values
  'Column 2 and 3 contain only numeric values
  'Argument 2 (dtBeg) is a Date value - default value 1/1 of current year
  'Argument 3 (dtEnd) is a Date value - default value today's date
  'Limit is numeric - 99999999999
  
  
  Dim colDt, colNote, colProfit
  Dim rowStart, rowEnd
  Dim rngtmp As Range
  Dim sheet As Worksheet
  Dim nrows As Integer
  Dim noteSum
  Dim profiSum
  
  
  Set sheet = rng.Parent
  tmp = Split(rng.Address, ":")
  Set rngtmp = sheet.Range(tmp(0))
  colDt = rngtmp.Column
  rowStart = rngtmp.Row
  
  Set rngtmp = sheet.Range(tmp(1))
  colProfit = rngtmp.Column
  rowEnd = rngtmp.Row
  
  colNote = colProfit - colDt
  nrows = rng.Rows.Count
  
  
  
  For r = rowStart To rowEnd
    Set rngtmp = sheet.Cells(r, colDt)
    dttmp = rngtmp
    If dttmp >= dtBeg And dttmp <= dtEnd Then
      If noteSum < Limit Then
        noteSum = noteSum + sheet.Cells(r, colNote).Value
        profitSum = profitSum + sheet.Cells(r, colProfit).Value
      End If
    End If
  Next r
  
  SumConditional = profitSum
End Function

 

[drmcbiz]

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

 

[Peter_SSs]

Welcome to the MrExcel board!

I want to sum up the profit made (column D) on the first $500 of notes exercised

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?