Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line chart - date line association gone mad! | Charts and Charting in Excel | |||
Date sort problems | Excel Discussion (Misc queries) |