Count data entries and date problem

[Gef]

Hi

As part of a loan management process I need to calculate
the value of loans outstanding with a specific bank, the
number of loans for that bank and the earliest repayment
date.

Sample data:

Bank Loan Repayment Date
Bank A 1000 01/10/2004
Bank A
Bank A 2000 05/10/2004
Bank A
Bank A
Bank A
Bank A
Bank A
Bank B 1200 01/09/2004
Bank B
Bank B 1000 11/09/2004
Bank B
Bank B 500 15/10/2004
Bank B
Bank B
Bank C 1000 20/10/2004
Bank C
Bank C
Bank C
Bank D 12000 19/10/2004
Bank D
Bank D


Bank Total Loans Count of loans Earliest Repayment
Date
Bank A 3000
Bank B 2700
Bank C 1000
Bank D 12000


I can find the value but am struggling with the count and
earliest repayment (by bank). Can anyone help?

 

[NHarkawat]

Value of Loan use function sumif(...
For Number of Loans use Countif(...
and earliest repayment date use
=min(if(A1:A100="Bank A"),(c1:C100)) and array enter it (press
ctrl+shift+enter)
where col A hods the bank name and col c holds the earliest repayment date

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A100="Bank A"),--(B2:B100<>""))

will count Bank A where loan is not blank, you can rewrite it to

=SUMPRODUCT(--(A2:A100="Bank A"),--(ISNUMBER(B2:B100)))


if there can be text in loan column that shouldn't be counted


for repayment date for Bank A


=MIN(IF((A2:A100="Bank A")*(B2:B100<>""),C2:C100))

entered with ctrl + shift & enter



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Max]

One way ..

Assume the sample data is in Sheet1, cols A to C
data from row2 down
(Col A = Bank, col B = Loan, col C = Repay Date)

In Sheet2
------------
Col headers in A11 are: Bank, Total Loan, # of Loans, Earliest Repay Date
In A2: A5 are listed: Bank A, Bank B, Bank C and Bank D

Put in:

B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

C2: =SUMPRODUCT((Sheet1!$A$2:$B$25=A2)*(Sheet1!$B$2:$B$25<>""))

D2:
=MIN(IF((Sheet1!$A$2:$A$25=A2)*(Sheet1!$C$2:$C$25<>""),Sheet1!$C$2:$C$25))
Array-enter formula in D2 with CTRL+SHIFT+ENTER
Format D2 as date

Select B22, copy down to D5

Adjust the ranges ($A$2:$A$25, $B$2:$B$25, etc) to suit

 

[GEF]

Thanks for all three replies - I have now sorted the
problem with your help

 

[K.S.Warrier]

hi,
when you require the earliest repayment date order, select the 3 columns
from the third (staring from the second row downwards) to the first.Click the
sort option A to Z, or Z to A.This automatically sorts the third column in
cronological order.Try this & thank you.
K.S.Warrier.