Possibly:
=SUMIF(A1:A10,"<="&X1,C1:C10)
where X1 contains the date to check.
I have following values:
(1) Loan agr. start date (col. A)
(2) Loan agr. end date (col. B)
(3) Loan amount (col. C)
And I have to find the amount of balance outstanding as at particular date.
Example:
1. 08/11/2013 - 08/13/2013 3500 USD
2. 08/12/2013 - 08/15/2013 5000 USD
3. 08/14/2013 - 08/15/2013 1000 USD
So, in 08/13/2013 the balance outstanding was 8500 USD. Could anyone help with formulas?![]()
Possibly:
=SUMIF(A1:A10,"<="&X1,C1:C10)
where X1 contains the date to check.
Can you post a sample of the data in a spreadsheet attachment?
How about
=SUMIFS(C1:C3,A1:A3,"<=" & X1,B1:B3,">=" & X1)
where X1 is the date to check
I'm using Excel 2010
I presume that the formula is to operate on the assumption that the loans are all paid off on the agreement end date, in which case the NoS one gives the correct figure. In reality, the repayments could be early or late, meaning that you need a repayment date for an accurate balance ?
Bookmarks