Forum Discussion

sandipgumtya365's avatar
sandipgumtya365
Brass Contributor
Nov 25, 2020
Solved

Need help with Tables

Hello EXCELers, Need some help with a formula. I was preparing a loan amortization table where I wanted to get the yearly interest paid. I have used the Sumproduct function to get the yearly data. T...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 27, 2020

    sandipgumtya365 

    Creating PivotTable add data to data model and instead of direct aggregation (sum) use measures like

    Paid Interest:=
VAR total=SUM(Home_Loan[Interest Paid])
VAR Result=IF(total=0,BLANK(),total)
RETURN Result

    Result will be like

    As a source I took the table to the left. As a comment, please don't add totals manually, Table support total row you may switch on/off and use the same formulas. Otherwise that could be side effect.

Craig Hatmaker's avatar
Craig Hatmaker
Iron Contributor
Nov 25, 2020

sandipgumtya365 There are many ways to approach this. I think the main problem you are having is how Excel uses fill with tables. Don't use fill for this. Copy the first cell and paste to other cells to preserve the table reference. 

 

I would also encourage using SUMIFS() instead of SUMPRODUCT. They both work but this version will copy/paste right correctly. 

 

=SUMIFS(HomeLoan[Interest Paid],HomeLoan[Months], ">" & (E1-1)*12,HomeLoan[Months], "<=" & E1*12 )

Resources