I use both Excel 2013 & 2016
A1 has the header "Date"
Cells A2-A25 has the values 1/1/2016, 2/1/2016 and so on formatted/displayed as Jan-16, Feb-16, etc
B1 has the header "Amount"
Cells B2-B25 has values $1, $2, ... $24
Cell E1 has a variable value: it currently has 9/1/2017 (displayed as Sep-17), but can change
Cell F1 has a variable value: currently has 12, but can change
The result for the above scenario for Cell C22 (next to Sep-17 $21) will be $186 which is the sum of B11:B22.
If E1 was changed to 8/1/2017, then cell C21 will be $174 which is the sum of B10:B21. Keeping 8/1/2017 and changing cell F1 to 10, the sum in cell C21 would now be $155 which is the sum of B12:B21.
Both variable values in E1 and F1 are driven by a bunch of other stuff. Too many details to get into here, but what I'm trying to accomplish is the same.
Essentially the value in E1 drives my END point and F1 drives how many rows in column B need summed ending on the Date Selected in cell E1.
Dates will be input in Ascending order with newest dates input on the next available row (currently row 26 would be the next row that the user would input 1/1/2018).
I will have a SUM formula in Column C for all 24 rows of data.
I'm not 100% sure on this next piece, but I think every result in Column C can be $0 where the DATE is NOT the same date as cell E1. Meaning I only need the total for Row 22 where cell A22 says Sep-17 because E1 is also Sep-17. But my formula in Column C needs to be the same all the way because if E1 is changed to Aug-17 then I need the info to update automatically. Sorry if this isn't 100% clear.
ANOTHER TRICKY ITEM: Say 8/1/2016 was chosen and 12 was the value in F1. 8/1/2016 is on row # 9 therefore there are only 8 rows available to sum (1/1/2016-8/1/2016), therefore the sum needs to take the MIN of 8 or 12, 8 in this case, so the sum range would be B2:B9 and total $36. Hope that makes sense. If the "8" needs calculated and stored in another cell, that is okay if that's what is needed.
I'd prefer not to use OFFSET as I've read that formula is more volatile. Does INDEX work better?
I hope this is possible, I'm just really struggling to figure out how to make this work. Many thanks for the assistance!
A1 has the header "Date"
Cells A2-A25 has the values 1/1/2016, 2/1/2016 and so on formatted/displayed as Jan-16, Feb-16, etc
B1 has the header "Amount"
Cells B2-B25 has values $1, $2, ... $24
Cell E1 has a variable value: it currently has 9/1/2017 (displayed as Sep-17), but can change
Cell F1 has a variable value: currently has 12, but can change
The result for the above scenario for Cell C22 (next to Sep-17 $21) will be $186 which is the sum of B11:B22.
If E1 was changed to 8/1/2017, then cell C21 will be $174 which is the sum of B10:B21. Keeping 8/1/2017 and changing cell F1 to 10, the sum in cell C21 would now be $155 which is the sum of B12:B21.
Both variable values in E1 and F1 are driven by a bunch of other stuff. Too many details to get into here, but what I'm trying to accomplish is the same.
Essentially the value in E1 drives my END point and F1 drives how many rows in column B need summed ending on the Date Selected in cell E1.
Dates will be input in Ascending order with newest dates input on the next available row (currently row 26 would be the next row that the user would input 1/1/2018).
I will have a SUM formula in Column C for all 24 rows of data.
I'm not 100% sure on this next piece, but I think every result in Column C can be $0 where the DATE is NOT the same date as cell E1. Meaning I only need the total for Row 22 where cell A22 says Sep-17 because E1 is also Sep-17. But my formula in Column C needs to be the same all the way because if E1 is changed to Aug-17 then I need the info to update automatically. Sorry if this isn't 100% clear.
ANOTHER TRICKY ITEM: Say 8/1/2016 was chosen and 12 was the value in F1. 8/1/2016 is on row # 9 therefore there are only 8 rows available to sum (1/1/2016-8/1/2016), therefore the sum needs to take the MIN of 8 or 12, 8 in this case, so the sum range would be B2:B9 and total $36. Hope that makes sense. If the "8" needs calculated and stored in another cell, that is okay if that's what is needed.
I'd prefer not to use OFFSET as I've read that formula is more volatile. Does INDEX work better?
I hope this is possible, I'm just really struggling to figure out how to make this work. Many thanks for the assistance!