Count data entries and date problem
 

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?

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

"Gef" wrote in message
...
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?

=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)



"Gef" wrote in message
...
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?

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 A1:D1 a 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 B2:D2, copy down to D5

Adjust the ranges ($A$2:$A$25, $B$2:$B$25, etc) to suit
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
---
"Gef" wrote in message
...
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?

Thanks for all three replies - I have now sorted the
problem with your help
-----Original Message-----
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?


.

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.

"GEF" wrote:

Thanks for all three replies - I have now sorted the
problem with your help
-----Original Message-----
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?


.