CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Sweepster
Sweepster

Reputation: 1949

Google Sheets Formula to obtain the last value in a column whose date is the max between a date range

In my Summary worksheet, I want cell A1 to display the last value from my Final Tracker worksheet in a column J whose date in column B is the last row between a date range identified in Summary G8 and G9.

Sample data (Dates are in dd-mm-yyyy format):

Cell Summary!G8 = 01-04-2024
Cell Summary!G9 = 30-04-2024

Final Tracker Worksheet (Data starts at B6):

Row Date (Column B) Value (Column J)
6 01-04-2024 $42.73
7 02-04-2024 $106.28
8 02-04-2024 $74.42
9 05-04-2024 $6.25
10 05-04-2024 $40.38
11 05-04-2024 $75.13
12 01-05-2024 $13.56

The result should be $75.13

The total number of rows will grow over time so the range cannot be hardcoded.

I've tried an approach using this formula but it returns a value that doesn't even exist in my data set:

=IF(SUMPRODUCT(('Final Tracker'!B:B >= G8) * ('Final Tracker'!B:B <= G9)) = 0, "", 
  INDEX('Final Tracker'!J:J, SUMPRODUCT(('Final Tracker'!B:B >= G8) * ('Final Tracker'!B:B <= G9)) * ROW('Final Tracker'!B:B)) / SUMPRODUCT(('Final Tracker'!B:B >= G8) * ('Final Tracker'!B:B <= G9)))

Upvotes: 0

Views: 26

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30240

You may try:

=chooserows(filter('Final Tracker'!J6:J,isbetween('Final Tracker'!B6:B,G8,G9)),-1)
  • Adjust the ranges accordingly if they aren't...

Upvotes: 1

Related Questions