• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Making bank summary in excel.

S

Shabbo

Member
Dear Sir,
I have large set of EMI repayment files I wanted to make summary as per sheet2 based on sheet1.
Please help.
 

Attachments

  • emi repayment file.xlsx
    15.9 KB · Views: 13
Copy the formula and paste on your sheet2 Cell D2 & D8
=SUMIFS(Sheet1!$E$2:$E$65,Sheet1!$B$2:$B$65,">="&$B2,Sheet1!$B$2:$B$65,"<="&$C2)
Paste this to D3 & D9
=SUMIFS(Sheet1!$F$2:$F$65,Sheet1!$B$2:$B$65,">="&$B3,Sheet1!$B$2:$B$65,"<="&$C3)
 
Hi,

Kindly use the table on data if you have large dataset and updating frequently, Assuming there is a unique loan number, a pivot table is best suited for such cases.
You can group the dates as quarters, and apply to the pivot table.

Veh NoSum of PincipleSum of Interest
A 474,316 242,756
B 401,037 136,767
c 438,657 99,147
D 479,806 57,998
E 380,966 14,186
Grand Total 2,174,782 550,853
 

Attachments

  • emi repayment file.xlsx
    26.4 KB · Views: 3
You must log in or register to reply here.
Back
Top