CODEWITHSUNDEEP
excelexcel-formula
user1283666
user1283666

Reputation: 11

How to sum a number of rows based on a value in a different cell?

I am trying to make a formula that will calculate how much of my mortgage I have paid in principal given the number of months I specify. I have a worksheet that has my Loan Amortization Schedule, and on a second worksheet I am trying to figure out how much of the principal I have paid down in 12 months, 24 months, 47 months, etc.

I would like to know how to calculate this. In one cell I'll enter the number of months (12, 24, 47, etc.) and then in a cell directly below it I will put the formula that will return all of the principal payments in a particular column on my Loan Amortization Schedule worksheet given whatever value I had put for the number of months (again 12, 24, 47, etc.).

Can someone plesae help?

Upvotes: 1

Views: 8301

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33175

This will calculate the amount of principle paid on a $160k mortgage at 5% on a 15 year amortization up to the month in cell H7

=CUMPRINC(0.05/12,15*12,160000,1,H7,0)

Upvotes: 0

assylias
assylias

Reputation: 328835

You can use the VLOOKUP function.

Sheet1:

       A      B
1    Month  Value
2      1     100
3      2     200
4      3     300
5      4     400
6      5     500

Sheet2

        A         B
1     Month    4
2     Value    =VLOOKUP(B1,Sheet1!A:B,2,FALSE)

EDIT following your comment, you want a sum up to that month, which could be done this way:

        A         B
1     Month    4
2     Value    =SUMIF(Sheet1!A:A,"<="&B1,Sheet1!B:B)

Upvotes: 2

Related Questions