Apr 07 2022 08:19 AM
Apr 07 2022 08:19 AM
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.
Apr 07 2022 08:55 AM
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.
Apr 07 2022 11:52 AM
@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.
Apr 07 2022 02:35 PM
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)