Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gef
 
Posts: n/a
Default 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?


  #2   Report Post  
NHarkawat
 
Posts: n/a
Default

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?




  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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




  #4   Report Post  
Max
 
Posts: n/a
Default

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?




  #5   Report Post  
GEF
 
Posts: n/a
Default

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?


.



  #6   Report Post  
K.S.Warrier
 
Posts: n/a
Default

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?


.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line chart - date line association gone mad! Johannes Czernin Charts and Charting in Excel 5 January 17th 05 09:48 PM
Date sort problems Graham_Wright Excel Discussion (Misc queries) 2 January 4th 05 06:00 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"