Finding values for specific date

curly

New member
Joined
Apr 24, 2013
Messages
7
Reaction score
0
Points
0
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?:)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Possibly:

=SUMIF(A1:A10,"<="&X1,C1:C10)

where X1 contains the date to check.
 

curly

New member
Joined
Apr 24, 2013
Messages
7
Reaction score
0
Points
0
Possibly:

=SUMIF(A1:A10,"<="&X1,C1:C10)

where X1 contains the date to check.

Nope... doesn't work. I thought that solution could be find in INDEX/MATCH formulas... but I am not an expert in these...
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Can you post a sample of the data in a spreadsheet attachment?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
How about

=SUMIFS(C1:C3,A1:A3,"<=" & X1,B1:B3,">=" & X1)

where X1 is the date to check

I'm using Excel 2010
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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 ?
 
Top