Hi again. You asked me to explain what the formula does so here I go.
Your original data sheet called "SJJ Clients" looks like this:
EazyExcel2.xlsx |
---|
|
---|
| A | B | C | D | E | F | G |
---|
112 | Payment Schedule | | | | | | |
---|
113 | Month | Date | Code | Made | Interest | Principal | Balance |
---|
114 | May-21 | 5/5/2021 | ABC | 400 | 200 | 220 | 1780 |
---|
115 | Jun-21 | | | | | | |
---|
116 | Jul-21 | | | | | | |
---|
|
---|
The formula resides in a table on the "Summary" sheet
Cell A3 has the 1st of the starting month date, in this case 4/1/2021 or 1st April 2021 as I have a US PC.
Cell A4 has =EOMONTH(A3,0)+1 and EOMONTH gives the last day of the provided date with a number of months added. I'm taking end of 4/1/2021 which is 4/30/2021 and adding zero months so it remains 4/30/2021 then I'm adding 1 to it because dates are held as integer values since 1 Jan 1900 so adding one day gets me 5/1/2021, the first day for the May summary. I do the same for A5 to get 6/1/2021.
Because you are using a Table then formulae will automatically extend so if you select D5 and press Tab it will add another row and populate the formulae so it's ready for July.
The SUMIFS formula for the only month with data, row 4, looks like this:
Excel Formula:
=SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!$B:$B,">="&Summary!$A4,'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A4,0))
SUMIFS has a parameter of the range to sum followed by pairs of criteria range and criteria.
The range we want to sum is on the SJJ Clients sheet in column D so we enter that SUMIFS('SJJ Clients'!D:D because column D contains the value to be summed.
Q. Why SUMIFS('SJJ Clients'!D:D and not SUMIFS('SJJ Clients'!$D:$D ?
A. Because we use relative addressing of column D as the Payment, Interest and Principal are next to each other in the same sequence so when I copy this formula to the right it will change D to E then F so we pick up the correct SJJ Clients columns.
Next are the criteria range and criteria. The criteria range is 'SJJ Clients'!$B:$B,">="&Summary!$A4 so it will check the Date of the SJJ Clients transaction and make sure it is greater than or equal to the summary date in Summary column A, in this case 5/1/2021.
Q. Why do some addresses have a $ and some not?
A. I want to make it so the formula can be copied across and down for the other values and months so I know I always want to check the date column in SJJ Clients so I make that address absolute with 'SJJ Clients'!$B:$B but the Summary month is also fixed in column A so I use $A but I want it to pick up other months so the row is relative and will change when copied down, hence Summary!$A4.
NOTE: One quirk of Excel is that some functions require the operators be wrapped in double quotes and the value appended with ampersand "&". e.g. if you want to check if X1 is not equal to Z1 then you use =IF(X1<>Z1,true,false) but if you want to use COUNTIF, SUMIF, etc then it requires COUNTIF(X1,"<>"&Z1)
Now comes the second criteria because the date from SJJ Clients can be any day of the month. We've already checked that the SJJ Clients dates are equal or greater than the 1st May 2021 so now we check the date is less than or equal to the last day of May,
'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A4,0)
I explained EOMONTH above so here we check all SJJ Client dates for being less than or equal to end of month A4 plus zero months, so it checks for date <= 31 May 2021.
I'm sure that's more of an explanation than you wanted so I'll stop now