sumifs dates

[whytewolves]

I am creating a transactions spreadsheet
I get paid on the first of every month
Usually, that payment actually comes in the last few days of the previous month
So when I go to sort my list by month, most of my payments are not listed because they were paid at the end of previous month
I need a fix for this please

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Month	Year	Date	Type	Bank	Acct	Acct #	Description	Category	Sub-Category	Bank2	Deposit(+)	Debit(-)	Check	Balance
2	January	2022	Friday, January 28, 2022	Income	Bank of America	Checking	1234	Direct Deposit	VA	VA Compensation		$ 4,961.73			$ 34,821.89
3	January	2022	Saturday, January 29, 2022	Expense	Bank of America	Checking	1234	Mortgage	Loans	Housing	Chase Bank	$ 1,236.00	$ (1,236.00)		$ 34,821.89
4	February	2022	Tuesday, February 1, 2022	Expense	Bank of America	Checking	1234	Electric	Housing	Alliant Energy			$ (136.32)		$ 34,685.57
5	January	1900													$ 34,685.57
6
7
8	Month	Year		Bank Accounts	Bank	Acct Name	Acct #	Starting Balance	Deposit	Debit	Balance
9	January	2022			Bank of America	Checking	1234	$ 6,892.73	$ 4,961.73	$ (1,236.00)	$ 10,618.46
10	Change Month to Verify				Chase Bank	Savings1	2345	$ 3,561.79	$ -		$ 3,561.79
11					Wells Fargo	Emergency Fund	3456	$ 15,679.91	$ -		$ 15,679.91
12					Total						$ 29,860.16
13
14				Bank Loans
15					Bank	Acct Name	Acct #	Starting Balance	Owe	Deposit	Balance
16					Bank of America	Car Loan	4567	$ 35,249.62	$ 12,376.00	$ -	$ 12,376.00
17					Chase Bank	Mortgage	5678	$ 467,932.00	$ 162,349.70	$ 1,236.00	$ 161,113.70
18					Wells Fargo	Personal Loan	6789	$ 10,537.00	$ 3,248.82	$ -	$ 3,248.82
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=TEXT([@Date],"mmmm")
B2:B5	B2	=IFERROR(YEAR([@Date]),"")
O2	O2	=Table2[[#Totals],[Balance]]+L2+M2
O3:O5	O3	=O2+[@[Deposit(+)]]+[@[Debit(-)]]
J9	J9	=SUMIFS(Table1[Debit(-)],Table1[Month],A9,Table1[Year],B9,Table1[Type],"expense",Table1[Bank],E9,Table1[Acct '#],G9)
I9:I11	I9	=SUMIFS(Table1[Deposit(+)],Table1[Month],A9,Table1[Year],B9,Table1[Type],"income",Table1[Bank],E9,Table1[Acct '#],G9)
K9:K11	K9	=H9+I9+J9
K12	K12	=SUBTOTAL(109,[Balance])
J16:J18	J16	=SUMIFS(Table1[Deposit(+)],Table1[Month],$A$9,Table1[Year],$B$9,Table1[Type],"expense",Table1[Category],"loans",Table1[Bank2],E16)
K16:K18	K16	=I16-J16

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	Expense, Income
E2	List	Wells Fargo, Bank of America, Chase Bank
F2:F5	List	Checking, Savings1, Savings2, Emergency Fund, Credit_Cards
E3:E5	List	Wells Fargo, Bank of America, Chase
K2:K5	List	Wells Fargo, Bank of America, Chase Bank
I2:I3	List	Credit_Cards,Loans, Other, Savings_Apps, Transfer, VA, Wages
I4	List	Credit_Cards,Loans, Other, Savings_Apps, Housing, Transfer, VA, Wages
I5	List	Credit_Cards,Loans, Other, Savings_Apps, Transfer, VA, Wages
A9	List	January, February, March, April
B9	List	2022, 2023, 2024

 

[JamesCanale]

Use something like this:

MrExcelPlayground6 (version 1).xlsb
	G	H
1	Date	Credited month
2	1/24/2022	1/1/2022
3	1/25/2022	2/1/2022
4	1/30/2022	2/1/2022
5	2/2/2022	2/1/2022
Sheet23

Cell Formulas
Range	Formula
H2:H5	H2	=EOMONTH(G2+7,-1)+1

 

[whytewolves]

Use something like this:

MrExcelPlayground6 (version 1).xlsb
	G	H
1	Date	Credited month
2	1/24/2022	1/1/2022
3	1/25/2022	2/1/2022
4	1/30/2022	2/1/2022
5	2/2/2022	2/1/2022
Sheet23

Cell Formulas
Range	Formula
H2:H5	H2	=EOMONTH(G2+7,-1)+1

So, I have the drop down to choose which month I am in: how do I add this formula to a drop down with the months listed????
Or, do I have is in the cell next to it and that be the cell referenced????

 

[JamesCanale]

I'd use this formula on the date of the transaction (put a column next to transaction date, call it "transaction month" and format the date without any day. Then when you select a month, transactions that occur in the last week of a month will be counted in the next month.

 

[whytewolves]

I'd use this formula on the date of the transaction (put a column next to transaction date, call it "transaction month" and format the date without any day. Then when you select a month, transactions that occur in the last week of a month will be counted in the next month.

Not getting it to work at all. Not bringing any information into the cells now.