Find the SUM of all cells in a column up to TODAY()

Question

I have a worksheet that contains data relating to a loan (Monthly Payments, Payment Dates, etc.). Column "A" contains a list of monthly payment dates, from the origination date in the first cell ("A10") to the final date in the last cell ("A94"). Column "B" contains a list of monthly payment amounts for each date listed in column "A".

I need to find the SUM of payments from the first payment ("B10") to the last payment prior to today's date.

Here is my formula to find the cell address in Column "B", which relates to the cell address in Column "A".

=CELL("address", INDEX(B10:B94, MATCH(MAXIFS(A10:A94, A10:A94, "<"&TODAY()), A10:A94, 0)))

What I need help with is how to incorporate the above formula into the SUM function, as the second half of the range. I have tried to incorporate the above formula into the SUM function:

=SUM(B10:CELL("address", INDEX(B10:B94, MATCH(MAXIFS(A10:A94, A10:A94, "<"&TODAY()), A10:A94, 0)))) but it just throws a #VALUE error.

Any help would be greatly appreciated.

Answer 1

CELL("address", ...) returns a text string (such as $B$42), not a cell reference. SUM cannot use that text as the end of a range, which is why you get #VALUE!.

You can use INDEX directly because it returns a reference:

=SUM(B10:INDEX(B10:B94, MATCH(MAXIFS(A10:A94, A10:A94, "<"&TODAY()), A10:A94, 0)))

If your goal is simply to sum all payments before today, a much simpler formula is:

=SUMIFS(B10:B94, A10:A94, "<"&TODAY())

This sums all values in column B where the corresponding date in column A is earlier than today, without needing MATCH, MAXIFS, or INDEX.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin