Forum Discussion
Put a MONTH function value in a SUM function
Hello,
I have the following SUM function in a sheet named Variance. It sums cells B11 to B22 from a sheet named 2025 Budget. The 2025 Budget cells are January (B11) to December (B22) budget values.
=SUM('2025 Budget'!B11:B22)
I have the following MONTH function in the 2025 Budget sheet. B6 is eom, entered as mm/dd/yy. When B6 is 1/31/25 the MONTH function cell value is B11, etc.
="B"&MONTH(B6)+10
I want to replace B22 in the above SUM function with the value of the above MONTH function. For example, if 6/30/25 is entered in B6 then the SUM function would sum B11:B16, the sum of January (B11) to June (B16) budget values.
I can't figure out how to do this!
Thank you,
HARS365
=SUM(INDIRECT("'2025 Budget'!B11:B"&MONTH('2025 Budget'!B6)+10))
or
=SUM(OFFSET('2025 Budget'!B11, 0, 0, MONTH('2025 Budget'!B6), 1))
5 Replies
Hi HansVogelaar and m_taylor,
Thank you very much for the OFFSET and INDEX formulas. They both work perfectly. You have saved me a ton of time doing budget what-ifs for 2025. I simply plug in the eom date and am good to go!
Thank you,
HARS365=SUM(INDIRECT("'2025 Budget'!B11:B"&MONTH('2025 Budget'!B6)+10))
or
=SUM(OFFSET('2025 Budget'!B11, 0, 0, MONTH('2025 Budget'!B6), 1))
I think Hans' second formula is missing the MONTH:
=SUM(OFFSET('2025 Budget'!B11, 0, 0, MONTH('2025 Budget'!B6), 1))
another alternative is:
=SUM( B11:INDEX(B11:B22, MONTH(B6)) )
You guys are doing so much better than I! You seem to have understood the problem for a start. I toyed with ideas such as
= LET( months, DATEDIF(startdate, targetdate, "m"), SUM(TAKE(amounts, months)) )but I was pretty clueless when it came to knowing whether it was even the right problem.
