eazyyexcel
New Member
- Joined
- Mar 1, 2021
- Messages
- 20
- Office Version
- 2016
- Platform
- Windows
Hello guys,
I am back with more excel headache! Hope someone will be able to help me out. @Toadstool helped me out the last time around. Thank you.
I have attached the XL2BB mini in this thread.
1. for the SUMIF formula under Summary sheet, under column F (reserved funds received), I need the same formula to capture the data in SJJ clients sheet under column K "reserved funds. The amount captured must be following the month calendar. Example 01/05/2021 to 31/05/2021 or 01/06/2021 to 30/06/2021.
2. In summary sheet, column G which is "personal investment", I need the data under SJJ clients to match and the total amount to be displayed under summary tab "personal investment". Example in SJJ clients sheet, B106 is "personal investment". The personal investment will need to capture cell A102 & B102. Example it says 1800. The formula will need to factor in "personal investment" OR "payment Received" (B106), and the amount transferred 1800 (A102 & B102) and the date borrowed (A99 & B99) so example for all the transactions from 01/05/2021 to 31/05/2021 will fall under May in summary sheet. Please take note that the client profiles are below each other so the list goes on. We could have up to 10 clients for example. so it basically needs to capture entire column, because below there might be another client who borrowed on a different date of the same month. All must be captured according to the specific month.
3. This is similar to point number 2. I need the same formula above for summary sheet column H (payment received investment). Hence under SJJ Clients sheet, it will capture all the data under 106, B106 that says "payment received". The total will be reflected under summary tab column H.
4. Total New clients under Summary sheet (column I). Same formulas apply but it will capture in SJJ clients sheet under cell A99 & B99 - date borrowed. Example it will give the total number of date borrowed based on the calendar date example 01/05/2021 to 31/05/2021. In my example I had listed 12 clients who were added from 01/05/2021 to 31/05/2021.
Thank you very much for all your help.
XLB2BB:
I am back with more excel headache! Hope someone will be able to help me out. @Toadstool helped me out the last time around. Thank you.
I have attached the XL2BB mini in this thread.
1. for the SUMIF formula under Summary sheet, under column F (reserved funds received), I need the same formula to capture the data in SJJ clients sheet under column K "reserved funds. The amount captured must be following the month calendar. Example 01/05/2021 to 31/05/2021 or 01/06/2021 to 30/06/2021.
2. In summary sheet, column G which is "personal investment", I need the data under SJJ clients to match and the total amount to be displayed under summary tab "personal investment". Example in SJJ clients sheet, B106 is "personal investment". The personal investment will need to capture cell A102 & B102. Example it says 1800. The formula will need to factor in "personal investment" OR "payment Received" (B106), and the amount transferred 1800 (A102 & B102) and the date borrowed (A99 & B99) so example for all the transactions from 01/05/2021 to 31/05/2021 will fall under May in summary sheet. Please take note that the client profiles are below each other so the list goes on. We could have up to 10 clients for example. so it basically needs to capture entire column, because below there might be another client who borrowed on a different date of the same month. All must be captured according to the specific month.
3. This is similar to point number 2. I need the same formula above for summary sheet column H (payment received investment). Hence under SJJ Clients sheet, it will capture all the data under 106, B106 that says "payment received". The total will be reflected under summary tab column H.
4. Total New clients under Summary sheet (column I). Same formulas apply but it will capture in SJJ clients sheet under cell A99 & B99 - date borrowed. Example it will give the total number of date borrowed based on the calendar date example 01/05/2021 to 31/05/2021. In my example I had listed 12 clients who were added from 01/05/2021 to 31/05/2021.
Thank you very much for all your help.
XLB2BB:
| money lending business SJJ test.xlsx | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 93 | Client Info | Remarks | ||||||||||||||||||||||
| 94 | Transaction Code | |||||||||||||||||||||||
| 95 | Name | |||||||||||||||||||||||
| 96 | Contact | |||||||||||||||||||||||
| 97 | Home Address | |||||||||||||||||||||||
| 98 | Work Address | |||||||||||||||||||||||
| 99 | Date Borrowed | 8/4/2021 | ||||||||||||||||||||||
| 100 | Monthly Payment Due Date | |||||||||||||||||||||||
| 101 | Amount Borrowed | 2,000.00 | ||||||||||||||||||||||
| 102 | Amount Transferred | 1,800.00 | ||||||||||||||||||||||
| 103 | Transfer method | |||||||||||||||||||||||
| 104 | Interest | |||||||||||||||||||||||
| 105 | Client Bank Account | |||||||||||||||||||||||
| 106 | Our Investment (Personal / Payment Received) | personal investment | ||||||||||||||||||||||
| 107 | Guarantor | |||||||||||||||||||||||
| 108 | Staff Signing | |||||||||||||||||||||||
| 109 | Loan agreement document | |||||||||||||||||||||||
| 110 | My reference | |||||||||||||||||||||||
| 111 | ||||||||||||||||||||||||
| 112 | Payment schedule | |||||||||||||||||||||||
| 113 | Month | Date | Payment Code | Payment Made | Interest | Principal | Balance | Jas Profit | Roopa Profit | Retained Earnings Acc | Reserved Funds | Paid out Y/N | Paid out Date | Payment Method (Cash Deposit / Cheque / Cash / Online Transfer | My bank account | Remarks | ||||||||
| 114 | May-21 | 15/5/2021 | 001-1PC | 700.00 | 200 | 500.00 | 1,500.00 | 500 | Online Transfer | Maybank | romelyn beria buegi*lyn | |||||||||||||
| 115 | Jun-21 | 1,500.00 | ||||||||||||||||||||||
| 116 | Jul-21 | 1,500.00 | ||||||||||||||||||||||
| 117 | Aug-21 | 1,500.00 | ||||||||||||||||||||||
| 118 | Sep-21 | 1,500.00 | ||||||||||||||||||||||
| 119 | Oct-21 | 1,500.00 | ||||||||||||||||||||||
| 120 | Nov-21 | 1,500.00 | ||||||||||||||||||||||
| 121 | Dec-21 | 1,500.00 | ||||||||||||||||||||||
| 122 | Jan-22 | 1,500.00 | ||||||||||||||||||||||
| 123 | Feb-22 | 1,500.00 | ||||||||||||||||||||||
| 124 | Mar-22 | 1,500.00 | ||||||||||||||||||||||
| 125 | Apr-22 | 1,500.00 | ||||||||||||||||||||||
| 126 | 700.00 | 200.00 | 500.00 | 0 | 0 | 0 | ||||||||||||||||||
SJJ Clients | ||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G114 | G114 | =SUM(B101-F114) |
| G115:G125 | G115 | =SUM(G114-F115) |
| H126:J126,D126:E126 | D126 | =SUM(D114:D125) |
| F126 | F126 | =SUM(F114:F124) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| L114:L125 | List | Y,N |
| N114:N125 | List | Cash Deposit,Cheque,Cash,Online Transfer |
| money lending business SJJ test.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | SJJ ACCOUNTS | |||||||||||
| 2 | Months | Months | Payment Received | Interest Received | Principal Received | Reserved Funds Received | Personal Investment | Payment Received Investment | Total New Clients | Column1 | ||
| 3 | April | 1/4/2021 | 50 | 50 | 0 | 0 | 31 | |||||
| 4 | May | 1/5/2021 | 6520 | 2550 | 3970 | 0 | 12 | |||||
Summary | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:E4 | C3 | =SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!$B:$B,">="&Summary!$B3,'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$B3,0)) |
| F4 | F4 | =SUMIFS('SJJ Clients'!G:G,'SJJ Clients'!$K:$K,">="&Summary!$K4,'SJJ Clients'!$K:$K,"<="&EOMONTH(Summary!$K4,0)) |
