SUMIF on Mortgage Amortization Sheet

New Contributor

I'm using a SUMIF formula to determine the remaining amount of interest on another sheet based on the number payment remaining on a mortgage. In the criteria, if I use a specific number, say payment "12", it calculates correctly. If I use a reference to a cell which would indicate the payment number, say "B2", it does not calculate. Any idea on how to make this criteria work? Thanks.

3 Replies

@Robert_Jeter 

Can you show us the actual formulas, the one that does work and the one that doesn't? It probably has to do with some minor aspect of how you've written it. 

And if you could describe more fully (or add a picture of) the spreadsheet that contains the data the formula is referring to, that could help as well.

@mathetes Sure. I've attached a few. The first is the actual mortgage amortization sheet. Calculates as it should. The second two are the specific formula I'm using SUMIF for to calculate the total amount of interest remaining to be paid. If I use the specific payment number, it calculates the total amount of interest. If I use a reference to the cell, it does not.

 

Robert_Jeter_0-1649357493243.pngRobert_Jeter_1-1649357535378.pngRobert_Jeter_2-1649357564248.png

 

@Robert_Jeter 

 

One of the reasons I enjoy answering questions here at the Excel techcommunity site is that I frequently learn something I didn't know. This is one of those cases.

 

Turns out that the syntax for SUMIF, when you're referencing the content of a cell, just isn't quite as straightforward as you might expect. Here's what I discovered:

 

A value from another cell can be included in criteria using concatenation. In the example below, SUMIF will return the sum of all sales over the value in G4.  Notice the greater than operator (>), which is text, must be enclosed in quotes. The formula in G5 is:

 

=SUMIF(D5:D9,">"&G4) // sum if greater than G4

The full page from which I got that reference is https://exceljet.net/excel-functions/excel-sumif-function

 

So your formula should end up reading thus:

=SUMIF('Mortgage Amortization'!A7:A366,">"&B3,'Mortgage Amortization'!D7:D366)