SUMIFS formula multiple criteria

[eazyyexcel]

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:

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