# SUMIFS formula multiple criteria

#### eazyyexcel

##### New Member
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:
ABCDEFGHIJKLMNOPQRSTUV
93Client InfoRemarks
94Transaction Code
95Name
96Contact
99Date Borrowed8/4/2021
100Monthly Payment Due Date
101Amount Borrowed2,000.00
102Amount Transferred1,800.00
103Transfer method
104Interest
105Client Bank Account
106Our Investment (Personal / Payment Received)personal investment
107Guarantor
108Staff Signing
109Loan agreement document
110My reference
111
112Payment schedule
113MonthDatePayment CodePayment MadeInterestPrincipalBalanceJas ProfitRoopa ProfitRetained Earnings AccReserved FundsPaid out Y/NPaid out DatePayment Method (Cash Deposit / Cheque / Cash / Online TransferMy bank accountRemarks
114May-2115/5/2021001-1PC700.00200500.001,500.00500Online TransferMaybankromelyn beria buegi*lyn
115Jun-211,500.00
116Jul-211,500.00
117Aug-211,500.00
118Sep-211,500.00
119Oct-211,500.00
120Nov-211,500.00
121Dec-211,500.00
122Jan-221,500.00
123Feb-221,500.00
124Mar-221,500.00
125Apr-221,500.00
126700.00200.00500.00000
SJJ Clients
Cell Formulas
RangeFormula
G114G114=SUM(B101-F114)
G115:G125G115=SUM(G114-F115)
H126:J126,D126:E126D126=SUM(D114:D125)
F126F126=SUM(F114:F124)
Cells with Data Validation
CellAllowCriteria
L114:L125ListY,N
N114:N125ListCash Deposit,Cheque,Cash,Online Transfer

ABCDEFGHIJ
1SJJ ACCOUNTS
3April1/4/202150500031
4May1/5/2021652025503970012
Summary
Cell Formulas
RangeFormula
C3:E4C3=SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!\$B:\$B,">="&Summary!\$B3,'SJJ Clients'!\$B:\$B,"<="&EOMONTH(Summary!\$B3,0))
F4F4=SUMIFS('SJJ Clients'!G:G,'SJJ Clients'!\$K:\$K,">="&Summary!\$K4,'SJJ Clients'!\$K:\$K,"<="&EOMONTH(Summary!\$K4,0))

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Replies
4
Views
264
Replies
10
Views
805
Replies
6
Views
861
Replies
3
Views
169
Replies
11
Views
966

1,191,399
Messages
5,986,365
Members
440,020
Latest member
IfsandSums

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back