Exceljet

Quick, clean, and to the point

Formula puzzle - sum payments by year

A reader sent me an interesting formula problem this week, so thought I'd share it as a formula challenge. The problem is this:

You have a fixed monthly payment, a start date, and a given number of months. What formula can you use to sum total payments by year, based on the following worksheet:

What formula works in E5, copied across to I5?

In other words, what formula works in E5, copied across to I5, to get a sum for each year shown?

I came up with a formula myself, but I'd love to see your ideas, too. If you're interested, leave a comment with the formula you propose.

You can use the following named ranges in your formula if you like: mos (C5), amount (C6), start (C7), end (C8).

You can download the worksheet below.

Note: the Disqus comment system we use on this site may mangle or truncate your formula when it contains greater than (>) or less than (<) symbols. To prevent, you need to entity-encode the symbols: use < for >, and > for <
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables