HELP! SUMIFS/Logic/Dates Functions

[Walker_Ice]

Hi Everyone,

I"ve been trying to figure out how to do this and been stuck for over a week. I created this custom report table, which works great. But the problem, I'm having is if I enter a "Start Date" which has a different year then my "End Year". It doesn't include the data from the different months in between the start year and end year.

For example, If I select 1/1/2022 until 3/30/2022. I gives me the correct data for those months because they are also in the same year.
But if I select 1/1/2022 until 3/30/2024, the formula I came up with doesn't know that it needs to get the data for all the months in between those dates because they are from different years.

I think I need an If statement, to resolve the problem. But I'm not sure. Can someone please take a look and help if possible? Below is the formula I am using with the SUMIFS function.

Thank you, in advance.

Income SUMIFS Function
=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&DATE(O22,MONTH($P$20),DAY($P$20)),$B$3:$B$29,"<="&DATE(O22,MONTH($R$20),DAY($R$20)))

Expenses SUMIFS Function
=SUMIFS($C$3:$C$29,$D$3:$D$29,">="&DATE(O22,MONTH($P$20),DAY($P$20)),$D$3:$D$29,"<="&DATE(O22,MONTH($R$20),DAY($R$20)))

 

[Habtest]

Welcome to the forum, try this with the revenue column

=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&MAX(DATE($O22,1,1),$P$20),$B$3:$B$29,"<="&MIN(DATE($O22,12,31),$R$20))

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

 

[Misca]

Try something like:

Mr Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Income Total	Income Date		Expense Total	Expense Date		CUSTOM REPORT
2	15	04.02.2022		2,87	30.11.2022		Start Date	01.01.2022	End Date	30.03.2024
3	10	12.03.2022		45,99	14.12.2022			Revenue	Costs	Profit
4	10	02.04.2022		6,81	11.01.2023		2022	220,00	48,86	171,14
5	5	13.05.2022		11,27	14.02.2023		2023	545,00	272,51	272,49
6	15	15.06.2022		14,7	17.03.2023		2024	590,00	382,69	207,31
7	50	07.07.2022		27,12	17.04.2023		2025	-	-	-
8	10	11.08.2022		42,06	06.05.2023		Total	1 355,00	704,06	650,94
Sheet4

Cell Formulas
Range	Formula
H4:H7	H4	=IF(($G4>=YEAR(StartDate))*($G4<=YEAR(EndDate)),SUMIFS(Income[Income Total],Income[Income Date],">="&StartDate,Income[Income Date],"<="&MIN(DATE($G4,12,31),EndDate)),0)
I4:I7	I4	=IF(($G4>=YEAR(StartDate))*($G4<=YEAR(EndDate)),SUMIFS(Expense[Expense Total],Expense[Expense Date],">="&StartDate,Expense[Expense Date],"<="&MIN(DATE(G4,12,31),EndDate)),0)
J4:J8	J4	=H4-I4
H8:I8	H8	=SUM(H4:H7)
G4	G4	=YEAR(StartDate)
G5:G7	G5	=G4+1

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet4!$J$2	H4:I7
StartDate	=Sheet4!$H$2	G4, H4:I7

I created separate Excel tables for the Incomes and Expenses + named ranges for the Start Date & End Date to make the formulas easier to read.

 

[Walker_Ice]

WOW! Thank you for your replies, this is my first time on a forum. But unfortunately, both methods did not work when I tried to implement them into my spreadsheet.

GiGIArt_V14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Income		Expenses
2	Income Total	Income Date	Expense _Total $	Expense_Date
3	$85.00	February 4, 2022	$18.99	11/30/2022
4	$100.00	March 13, 2023	$16.99	11/30/2022
5	$100.00	June 17, 2024	$46.99	11/30/2024
6	$70.00	April 25, 2023	$11.88	11/30/2022
7	$90.00	June 17, 2023	$32.99	11/30/2022													265			2890	1234.88
8	$90.00	June 6, 2024	$6.99	12/1/2022
9	$70.00	February 20, 2023	$11.85	12/8/2022												1445
10	$100.00	June 28, 2023	$9.99	12/8/2023
11	$85.00	June 27, 2022	$7.99	12/9/2023
12	$80.00	March 17, 2023	$6.99	12/10/2023												265
13	$90.00	June 7, 2023	$7.09	12/11/2023												1000
14	$95.00	July 10, 2022	$7.99	12/12/2023												95
15	$95.00	March 31, 2022	$17.76	12/13/2023												0
16	$80.00	May 26, 2023	$14.89	12/23/2022
17	$85.00	February 18, 2023	$16.99	12/23/2022
18	$70.00	June 1, 2023	$6.99	12/23/2022
19	$70.00	February 18, 2023	$9.99	12/30/2022											CUSTOM REPORT
20	$90.00	March 18, 2023	$36.99	11/30/2022											START DATE:	1/1/2022	END DATE:	5/30/2023
21	$85.00	May 28, 2022	$4.99	11/30/2022												Revenue	Total Cost	Profits
22	$10.00	February 8, 2023	$25.90	6/24/2023											2022	$0.00	$254.84	-$254.84
23	$10.00	March 3, 2023	$12.99	9/10/2022											2023	$445.00	$445.00	$0.00
24	$85.00	January 4, 2023	$23.97	9/10/2022											2024	$1,000.00	$0.00	$1,000.00
25	$85.00	February 14, 2023	$8.99	7/30/2022											2025	$0.00	$0.00	$0.00
26	$70.00	April 1, 2023	$16.98	7/30/2022											Total $	$1,445.00	$699.84	$745.16
27	$95.00	January 3, 2024	$19.78	7/30/2024
28	$95.00	February 25, 2023	$4.54	7/31/2024
29	$75.00	June 26, 2023	$6.49	8/1/2024
Sheet1

Cell Formulas
Range	Formula
Q7	Q7	=SUMPRODUCT(($B$3:$B$29>=DATE(YEAR(P20),MONTH(P20),DAY(P20))) * ($B$3:$B$29<=DATE(O22,MONTH(R20),DAY(R20))) * ($A$3:$A$29))
T7	T7	=SUM(SUMIFS(Income_TotalPay, Income_Dates, ">=" & DATE(O23, MONTH($P$20), DAY($P$20)), Income_Dates, "<=" & DATE(O23, MONTH($R$20), DAY($R$20))), SUMIFS(Income_TotalPay, Income_Dates, ">=" & DATE(O23+1, MONTH($P$20), DAY($P$20)), Income_Dates, "<=" & DATE(O23, MONTH($R$20), DAY($R$20))))
U7	U7	=SUM(SUMIFS(Expense_TotalSpent, Expense_Dates, ">=" & DATE(O23, MONTH($P$20), DAY($P$20)), Expense_Dates, "<=" & DATE(O23, MONTH($R$20), DAY($R$20))), SUMIFS(Expense_TotalSpent, Expense_Dates, ">=" & DATE(O23+1, MONTH($P$20), DAY($P$20)), Expense_Dates, "<=" & DATE(O23, MONTH($R$20), DAY($R$20))))
P9	P9	=SUMIFS($A$3:$A$29, $B$3:$B$29, ">=" & P20, $B$3:$B$29, "<=" & R20)
P12:P15	P12	=SUM(SUMIFS($A$3:$A$29, $B$3:$B$29, ">=" & DATE(O22, MONTH($P$20), DAY($P$20)), $B$3:$B$29, "<=" & DATE(O22, MONTH($R$20), DAY($R$20))), SUMIFS($A$3:$A$29, $B$3:$B$29, ">=" & DATE(O22+1, MONTH($P$20), DAY($P$20)), $B$3:$B$29, "<=" & DATE(O22, MONTH($R$20), DAY($R$20))))
P22	P22	=IF(($O22>=YEAR(P20))*($O22<=YEAR(R20)),SUMIFS(A3:A29,A3:A29,">="&P20,A3:A29,"<="&MIN(DATE($O22,12,31),R20)),0)
Q22	Q22	=SUMIFS(Expense_TotalSpent, Expense_Dates, ">="&DATE(O22, MONTH($P$20), DAY($P$20)), Expense_Dates, "<="&DATE(O22, MONTH($R$20), DAY($R$20)))
R22:R25	R22	=P22-Q22
P23:Q23,P24:P25	P23	=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&MAX(DATE($O22,1,1),$P$20),$B$3:$B$29,"<="&MIN(DATE($O22,12,31),$R$20))
Q24:Q25	Q24	=SUM(SUMIFS(Expense_TotalSpent, Expense_Dates, ">=" & DATE(O24, MONTH($P$20), DAY($P$20)), Expense_Dates, "<=" & DATE(O24, MONTH($R$20), DAY($R$20))), SUMIFS(Expense_TotalSpent, Expense_Dates, ">=" & DATE(O24+1, MONTH($P$20), DAY($P$20)), Expense_Dates, "<=" & DATE(O24, MONTH($R$20), DAY($R$20))))
P26:R26	P26	=SUM(P22:P25)

Named Ranges
Name	Refers To	Cells
Expense_Dates	=SuppliesExpenses!$B$3:$B$997	Q24:Q25, Q22, U7
Expense_TotalSpent	=SuppliesExpenses!$E$3:$E$997	Q24:Q25, Q22, U7
Income_Dates	='Income Statement'!$C$3:$C$992	T7
Income_TotalPay	='Income Statement'!$F$3:$F$992	T7

 

[Walker_Ice]

Welcome to the forum, try this with the revenue column

=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&MAX(DATE($O22,1,1),$P$20),$B$3:$B$29,"<="&MIN(DATE($O22,12,31),$R$20))

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Thank you, for getting back to me. I appreciate your support. Also, thank you for teaching me how to copy the mini table to the forum. I copied the spreadsheet to this forum below.

 

[Walker_Ice]

Try something like:

Mr Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Income Total	Income Date		Expense Total	Expense Date		CUSTOM REPORT
2	15	04.02.2022		2,87	30.11.2022		Start Date	01.01.2022	End Date	30.03.2024
3	10	12.03.2022		45,99	14.12.2022			Revenue	Costs	Profit
4	10	02.04.2022		6,81	11.01.2023		2022	220,00	48,86	171,14
5	5	13.05.2022		11,27	14.02.2023		2023	545,00	272,51	272,49
6	15	15.06.2022		14,7	17.03.2023		2024	590,00	382,69	207,31
7	50	07.07.2022		27,12	17.04.2023		2025	-	-	-
8	10	11.08.2022		42,06	06.05.2023		Total	1 355,00	704,06	650,94
Sheet4

Cell Formulas
Range	Formula
H4:H7	H4	=IF(($G4>=YEAR(StartDate))*($G4<=YEAR(EndDate)),SUMIFS(Income[Income Total],Income[Income Date],">="&StartDate,Income[Income Date],"<="&MIN(DATE($G4,12,31),EndDate)),0)
I4:I7	I4	=IF(($G4>=YEAR(StartDate))*($G4<=YEAR(EndDate)),SUMIFS(Expense[Expense Total],Expense[Expense Date],">="&StartDate,Expense[Expense Date],"<="&MIN(DATE(G4,12,31),EndDate)),0)
J4:J8	J4	=H4-I4
H8:I8	H8	=SUM(H4:H7)
G4	G4	=YEAR(StartDate)
G5:G7	G5	=G4+1

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet4!$J$2	H4:I7
StartDate	=Sheet4!$H$2	G4, H4:I7

I created separate Excel tables for the Incomes and Expenses + named ranges for the Start Date & End Date to make the formulas easier to read.

Thank you for you reply!
I tried interpreting your code and translating it but couldn't get it to work. I have copied the mini table from my spreadsheet for to have a better reference below.

 

[Habtest]

Glad the XL2BB works out and makes things easier, please see below

Thank you, for getting back to me. I appreciate your support. Also, thank you for teaching me how to copy the mini table to the forum. I copied the spreadsheet to this forum below.

Book1
	A	B	C	D	E										O	P	Q	R
1	Income		Expenses
2	Income Total	Income Date	Expense _Total $	Expense_Date
3	$85.00	2/4/2022	$18.99	11/30/2022
4	$100.00	3/13/2023	$16.99	11/30/2022
5	$100.00	6/17/2024	$46.99	11/30/2024
6	$70.00	4/25/2023	$11.88	11/30/2022
7	$90.00	6/17/2023	$32.99	11/30/2022
8	$90.00	6/6/2024	$6.99	12/1/2022
9	$70.00	2/20/2023	$11.85	12/8/2022
10	$100.00	6/28/2023	$9.99	12/8/2023
11	$85.00	6/27/2022	$7.99	12/9/2023
12	$80.00	3/17/2023	$6.99	12/10/2023
13	$90.00	6/7/2023	$7.09	12/11/2023
14	$95.00	7/10/2022	$7.99	12/12/2023
15	$95.00	3/31/2022	$17.76	12/13/2023
16	$80.00	5/26/2023	$14.89	12/23/2022
17	$85.00	2/18/2023	$16.99	12/23/2022
18	$70.00	6/1/2023	$6.99	12/23/2022
19	$70.00	2/18/2023	$9.99	12/30/2022											CUSTOM REPORT
20	$90.00	3/18/2023	$36.99	11/30/2022											START DATE:	1/1/2022	END DATE:	5/30/2023
21	$85.00	5/28/2022	$4.99	11/30/2022												Revenue	Total Cost	Profits
22	$10.00	2/8/2023	$25.90	6/24/2023											2022	$445.00	$253.46	$191.54
23	$10.00	3/3/2023	$12.99	9/10/2022											2023	$1,000.00	$0.00	$1,000.00
24	$85.00	1/4/2023	$23.97	9/10/2022											2024	$0.00	$0.00	$0.00
25	$85.00	2/14/2023	$8.99	7/30/2022											2025	$0.00	$0.00	$0.00
26	$70.00	4/1/2023	$16.98	7/30/2022											Total $	$1,445.00	$253.46	$1,191.54
27	$95.00	1/3/2024	$19.78	7/30/2024
28	$95.00	2/25/2023	$4.54	7/31/2024
29	$75.00	6/26/2023	$6.49	8/1/2024
Sheet1

Cell Formulas
Range	Formula
P22:P25	P22	=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&MAX(DATE($O22,1,1),$P$20),$B$3:$B$29,"<="&MIN(DATE($O22,12,31),$R$20))
Q22:Q25	Q22	=SUMIFS($C$3:$C$29,$D$3:$D$29,">="&MAX(DATE($O22,1,1),$P$20),$D$3:$D$29,"<="&MIN(DATE($O22,12,31),$R$20))
R22:R25	R22	=P22-Q22
P26:R26	P26	=SUM(P22:P25)

 

[Walker_Ice]

Glad the XL2BB works out and makes things easier, please see below

Book1
	A	B	C	D	E										O	P	Q	R
1	Income		Expenses
2	Income Total	Income Date	Expense _Total $	Expense_Date
3	$85.00	2/4/2022	$18.99	11/30/2022
4	$100.00	3/13/2023	$16.99	11/30/2022
5	$100.00	6/17/2024	$46.99	11/30/2024
6	$70.00	4/25/2023	$11.88	11/30/2022
7	$90.00	6/17/2023	$32.99	11/30/2022
8	$90.00	6/6/2024	$6.99	12/1/2022
9	$70.00	2/20/2023	$11.85	12/8/2022
10	$100.00	6/28/2023	$9.99	12/8/2023
11	$85.00	6/27/2022	$7.99	12/9/2023
12	$80.00	3/17/2023	$6.99	12/10/2023
13	$90.00	6/7/2023	$7.09	12/11/2023
14	$95.00	7/10/2022	$7.99	12/12/2023
15	$95.00	3/31/2022	$17.76	12/13/2023
16	$80.00	5/26/2023	$14.89	12/23/2022
17	$85.00	2/18/2023	$16.99	12/23/2022
18	$70.00	6/1/2023	$6.99	12/23/2022
19	$70.00	2/18/2023	$9.99	12/30/2022											CUSTOM REPORT
20	$90.00	3/18/2023	$36.99	11/30/2022											START DATE:	1/1/2022	END DATE:	5/30/2023
21	$85.00	5/28/2022	$4.99	11/30/2022												Revenue	Total Cost	Profits
22	$10.00	2/8/2023	$25.90	6/24/2023											2022	$445.00	$253.46	$191.54
23	$10.00	3/3/2023	$12.99	9/10/2022											2023	$1,000.00	$0.00	$1,000.00
24	$85.00	1/4/2023	$23.97	9/10/2022											2024	$0.00	$0.00	$0.00
25	$85.00	2/14/2023	$8.99	7/30/2022											2025	$0.00	$0.00	$0.00
26	$70.00	4/1/2023	$16.98	7/30/2022											Total $	$1,445.00	$253.46	$1,191.54
27	$95.00	1/3/2024	$19.78	7/30/2024
28	$95.00	2/25/2023	$4.54	7/31/2024
29	$75.00	6/26/2023	$6.49	8/1/2024
Sheet1

Cell Formulas
Range	Formula
P22:P25	P22	=SUMIFS($A$3:$A$29,$B$3:$B$29,">="&MAX(DATE($O22,1,1),$P$20),$B$3:$B$29,"<="&MIN(DATE($O22,12,31),$R$20))
Q22:Q25	Q22	=SUMIFS($C$3:$C$29,$D$3:$D$29,">="&MAX(DATE($O22,1,1),$P$20),$D$3:$D$29,"<="&MIN(DATE($O22,12,31),$R$20))
R22:R25	R22	=P22-Q22
P26:R26	P26	=SUM(P22:P25)

That works PERFECTLY!!! I can't thank you enough, THANK YOU FOR YOUR HELP.

 

[Alex Blakenburg]

Does this work for you ?

20231007 SumIfs vs Filter Walker_Ice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Income		Expenses
2	Income Total	Income Date	Expense _Total $	Expense_Date
3	85	4/02/2022	18.99	30/11/2022
4	100	13/03/2023	16.99	30/11/2022
5	100	17/06/2024	46.99	30/11/2024
6	70	25/04/2023	11.88	30/11/2022
7	90	17/06/2023	32.99	30/11/2022
8	90	6/06/2024	6.99	1/12/2022
9	70	20/02/2023	11.85	8/12/2022
10	100	28/06/2023	9.99	8/12/2023
11	85	27/06/2022	7.99	9/12/2023
12	80	17/03/2023	6.99	10/12/2023
13	90	7/06/2023	7.09	11/12/2023
14	95	10/07/2022	7.99	12/12/2023
15	95	31/03/2022	17.76	13/12/2023
16	80	26/05/2023	14.89	23/12/2022
17	85	18/02/2023	16.99	23/12/2022
18	70	1/06/2023	6.99	23/12/2022
19	70	18/02/2023	9.99	30/12/2022											CUSTOM REPORT
20	90	18/03/2023	36.99	30/11/2022											START DATE:	1/01/2022	END DATE:	30/05/2023
21	85	28/05/2022	4.99	30/11/2022												Revenue	Total Cost	Profits
22	10	8/02/2023	25.9	24/06/2023											2022	445	253.46	191.54
23	10	3/03/2023	12.99	10/09/2022											2023	1000	0	1000
24	85	4/01/2023	23.97	10/09/2022											2024	0	0	0
25	85	14/02/2023	8.99	30/07/2022											2025	0	0	0
26	70	1/04/2023	16.98	30/07/2022											Total $	1445	253.46	1191.54
27	95	3/01/2024	19.78	30/07/2024
28	95	25/02/2023	4.54	31/07/2024
29	75	26/06/2023	6.49	1/08/2024
Sheet1

Cell Formulas
Range	Formula
P22:P25	P22	=LET(rDate,$B$3:$B$29, rAmt,$A$3:$A$29, SUM( FILTER(rAmt, (rDate>=$P$20)*(rDate<=$R$20)*(YEAR(rDate)=$O22), 0) ))
Q22:Q25	Q22	=LET(rDate,$D$3:$D$29, rAmt,$C$3:$C$29, SUM( FILTER(rAmt, (rDate>=$P$20)*(rDate<=$R$20)*(YEAR(rDate)=$O22), 0) ))
R22:R25	R22	=P22-Q22
P26:R26	P26	=SUM(P22:P25)

 

[Walker_Ice]

Does this work for you ?

20231007 SumIfs vs Filter Walker_Ice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Income		Expenses
2	Income Total	Income Date	Expense _Total $	Expense_Date
3	85	4/02/2022	18.99	30/11/2022
4	100	13/03/2023	16.99	30/11/2022
5	100	17/06/2024	46.99	30/11/2024
6	70	25/04/2023	11.88	30/11/2022
7	90	17/06/2023	32.99	30/11/2022
8	90	6/06/2024	6.99	1/12/2022
9	70	20/02/2023	11.85	8/12/2022
10	100	28/06/2023	9.99	8/12/2023
11	85	27/06/2022	7.99	9/12/2023
12	80	17/03/2023	6.99	10/12/2023
13	90	7/06/2023	7.09	11/12/2023
14	95	10/07/2022	7.99	12/12/2023
15	95	31/03/2022	17.76	13/12/2023
16	80	26/05/2023	14.89	23/12/2022
17	85	18/02/2023	16.99	23/12/2022
18	70	1/06/2023	6.99	23/12/2022
19	70	18/02/2023	9.99	30/12/2022											CUSTOM REPORT
20	90	18/03/2023	36.99	30/11/2022											START DATE:	1/01/2022	END DATE:	30/05/2023
21	85	28/05/2022	4.99	30/11/2022												Revenue	Total Cost	Profits
22	10	8/02/2023	25.9	24/06/2023											2022	445	253.46	191.54
23	10	3/03/2023	12.99	10/09/2022											2023	1000	0	1000
24	85	4/01/2023	23.97	10/09/2022											2024	0	0	0
25	85	14/02/2023	8.99	30/07/2022											2025	0	0	0
26	70	1/04/2023	16.98	30/07/2022											Total $	1445	253.46	1191.54
27	95	3/01/2024	19.78	30/07/2024
28	95	25/02/2023	4.54	31/07/2024
29	75	26/06/2023	6.49	1/08/2024
Sheet1

Cell Formulas
Range	Formula
P22:P25	P22	=LET(rDate,$B$3:$B$29, rAmt,$A$3:$A$29, SUM( FILTER(rAmt, (rDate>=$P$20)*(rDate<=$R$20)*(YEAR(rDate)=$O22), 0) ))
Q22:Q25	Q22	=LET(rDate,$D$3:$D$29, rAmt,$C$3:$C$29, SUM( FILTER(rAmt, (rDate>=$P$20)*(rDate<=$R$20)*(YEAR(rDate)=$O22), 0) ))
R22:R25	R22	=P22-Q22
P26:R26	P26	=SUM(P22:P25)

I ended up using a version of your solution because I was getting errors sometimes, when I used the other solution. Below is the version that I landed on which doesn't give any errors.

So for future references, below is the solution for the problem I initially posted about.

Income_SUM:

=IFERROR(SUM(FILTER(Income_TotalPay, (Income_Dates >= $L$25) * (Income_Dates <= $N$25) * (YEAR(Income_Dates) = $K27))), 0)

Expenses_SUM:

=IFERROR(SUM(FILTER(Expense_TotalSpent, (Expense_Dates >= $L$25) * (Expense_Dates <= $N$25) * (YEAR(Expense_Dates) = $K27))),0)