HELP! SUMIFS/Logic/Dates Functions

W

Walker_Ice

Board Regular
Joined
Oct 6, 2023
Messages
50
Office Version
  1. 2021
Platform
  1. MacOS
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)))

Screenshot 2023-10-07 at 2.52.38 PM.png
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Welcome to the forum, try this with the revenue column
Excel Formula: 
=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
 
Upvote 0
Try something like:
Mr Excel.xlsx
ABCDEFGHIJK
1Income TotalIncome DateExpense TotalExpense DateCUSTOM REPORT
21504.02.20222,8730.11.2022Start Date01.01.2022End Date30.03.2024
31012.03.202245,9914.12.2022RevenueCostsProfit
41002.04.20226,8111.01.20232022220,0048,86171,14
5513.05.202211,2714.02.20232023545,00272,51272,49
61515.06.202214,717.03.20232024590,00382,69207,31
75007.07.202227,1217.04.20232025---
81011.08.202242,0606.05.2023Total1 355,00704,06650,94
Sheet4
Cell Formulas
RangeFormula
H4:H7H4=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:I7I4=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:J8J4=H4-I4
H8:I8H8=SUM(H4:H7)
G4G4=YEAR(StartDate)
G5:G7G5=G4+1
Named Ranges
NameRefers ToCells
EndDate=Sheet4!$J$2H4:I7
StartDate=Sheet4!$H$2G4, 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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTU
1IncomeExpenses
2Income TotalIncome DateExpense _Total $Expense_Date
3$85.00February 4, 2022$18.9911/30/2022
4$100.00March 13, 2023$16.9911/30/2022
5$100.00June 17, 2024$46.9911/30/2024
6$70.00April 25, 2023$11.8811/30/2022
7$90.00June 17, 2023$32.9911/30/202226528901234.88
8$90.00June 6, 2024$6.9912/1/2022
9$70.00February 20, 2023$11.8512/8/20221445
10$100.00June 28, 2023$9.9912/8/2023
11$85.00June 27, 2022$7.9912/9/2023
12$80.00March 17, 2023$6.9912/10/2023265
13$90.00June 7, 2023$7.0912/11/20231000
14$95.00July 10, 2022$7.9912/12/202395
15$95.00March 31, 2022$17.7612/13/20230
16$80.00May 26, 2023$14.8912/23/2022
17$85.00February 18, 2023$16.9912/23/2022
18$70.00June 1, 2023$6.9912/23/2022
19$70.00February 18, 2023$9.9912/30/2022 CUSTOM REPORT
20$90.00March 18, 2023$36.9911/30/2022START DATE:1/1/2022END DATE:5/30/2023
21$85.00May 28, 2022$4.9911/30/2022RevenueTotal CostProfits
22$10.00February 8, 2023$25.906/24/20232022$0.00$254.84-$254.84
23$10.00March 3, 2023$12.999/10/20222023$445.00$445.00$0.00
24$85.00January 4, 2023$23.979/10/20222024$1,000.00$0.00$1,000.00
25$85.00February 14, 2023$8.997/30/20222025$0.00$0.00$0.00
26$70.00April 1, 2023$16.987/30/2022Total $$1,445.00$699.84$745.16
27$95.00January 3, 2024$19.787/30/2024
28$95.00February 25, 2023$4.547/31/2024
29$75.00June 26, 2023$6.498/1/2024
Sheet1
Cell Formulas
RangeFormula
Q7Q7=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))
T7T7=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))))
U7U7=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))))
P9P9=SUMIFS($A$3:$A$29, $B$3:$B$29, ">=" & P20, $B$3:$B$29, "<=" & R20)
P12:P15P12=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))))
P22P22=IF(($O22>=YEAR(P20))*($O22<=YEAR(R20)),SUMIFS(A3:A29,A3:A29,">="&P20,A3:A29,"<="&MIN(DATE($O22,12,31),R20)),0)
Q22Q22=SUMIFS(Expense_TotalSpent, Expense_Dates, ">="&DATE(O22, MONTH($P$20), DAY($P$20)), Expense_Dates, "<="&DATE(O22, MONTH($R$20), DAY($R$20)))
R22:R25R22=P22-Q22
P23:Q23,P24:P25P23=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:Q25Q24=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:R26P26=SUM(P22:P25)
Named Ranges
NameRefers ToCells
Expense_Dates=SuppliesExpenses!$B$3:$B$997Q24:Q25, Q22, U7
Expense_TotalSpent=SuppliesExpenses!$E$3:$E$997Q24:Q25, Q22, U7
Income_Dates='Income Statement'!$C$3:$C$992T7
Income_TotalPay='Income Statement'!$F$3:$F$992T7
 
Upvote 0
Habtest said:
Welcome to the forum, try this with the revenue column
Excel Formula: 
=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
Click to expand...
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.
 
Upvote 0
Misca said:
Try something like:
Mr Excel.xlsx
ABCDEFGHIJK
1Income TotalIncome DateExpense TotalExpense DateCUSTOM REPORT
21504.02.20222,8730.11.2022Start Date01.01.2022End Date30.03.2024
31012.03.202245,9914.12.2022RevenueCostsProfit
41002.04.20226,8111.01.20232022220,0048,86171,14
5513.05.202211,2714.02.20232023545,00272,51272,49
61515.06.202214,717.03.20232024590,00382,69207,31
75007.07.202227,1217.04.20232025---
81011.08.202242,0606.05.2023Total1 355,00704,06650,94
Sheet4
Cell Formulas
RangeFormula
H4:H7H4=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:I7I4=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:J8J4=H4-I4
H8:I8H8=SUM(H4:H7)
G4G4=YEAR(StartDate)
G5:G7G5=G4+1
Named Ranges
NameRefers ToCells
EndDate=Sheet4!$J$2H4:I7
StartDate=Sheet4!$H$2G4, 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.
Click to expand...
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.
 
Upvote 0
Glad the XL2BB works out and makes things easier, please see below
Walker_Ice said:
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.
Click to expand...
Book1
ABCDEOPQR
1IncomeExpenses
2Income TotalIncome DateExpense _Total $Expense_Date
3$85.002/4/2022$18.9911/30/2022
4$100.003/13/2023$16.9911/30/2022
5$100.006/17/2024$46.9911/30/2024
6$70.004/25/2023$11.8811/30/2022
7$90.006/17/2023$32.9911/30/2022
8$90.006/6/2024$6.9912/1/2022
9$70.002/20/2023$11.8512/8/2022
10$100.006/28/2023$9.9912/8/2023
11$85.006/27/2022$7.9912/9/2023
12$80.003/17/2023$6.9912/10/2023
13$90.006/7/2023$7.0912/11/2023
14$95.007/10/2022$7.9912/12/2023
15$95.003/31/2022$17.7612/13/2023
16$80.005/26/2023$14.8912/23/2022
17$85.002/18/2023$16.9912/23/2022
18$70.006/1/2023$6.9912/23/2022
19$70.002/18/2023$9.9912/30/2022 CUSTOM REPORT
20$90.003/18/2023$36.9911/30/2022START DATE:1/1/2022END DATE:5/30/2023
21$85.005/28/2022$4.9911/30/2022RevenueTotal CostProfits
22$10.002/8/2023$25.906/24/20232022$445.00$253.46$191.54
23$10.003/3/2023$12.999/10/20222023$1,000.00$0.00$1,000.00
24$85.001/4/2023$23.979/10/20222024$0.00$0.00$0.00
25$85.002/14/2023$8.997/30/20222025$0.00$0.00$0.00
26$70.004/1/2023$16.987/30/2022Total $$1,445.00$253.46$1,191.54
27$95.001/3/2024$19.787/30/2024
28$95.002/25/2023$4.547/31/2024
29$75.006/26/2023$6.498/1/2024
Sheet1
Cell Formulas
RangeFormula
P22:P25P22=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:Q25Q22=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:R25R22=P22-Q22
P26:R26P26=SUM(P22:P25)
 
Upvote 0
Habtest said:
Glad the XL2BB works out and makes things easier, please see below

Book1
ABCDEOPQR
1IncomeExpenses
2Income TotalIncome DateExpense _Total $Expense_Date
3$85.002/4/2022$18.9911/30/2022
4$100.003/13/2023$16.9911/30/2022
5$100.006/17/2024$46.9911/30/2024
6$70.004/25/2023$11.8811/30/2022
7$90.006/17/2023$32.9911/30/2022
8$90.006/6/2024$6.9912/1/2022
9$70.002/20/2023$11.8512/8/2022
10$100.006/28/2023$9.9912/8/2023
11$85.006/27/2022$7.9912/9/2023
12$80.003/17/2023$6.9912/10/2023
13$90.006/7/2023$7.0912/11/2023
14$95.007/10/2022$7.9912/12/2023
15$95.003/31/2022$17.7612/13/2023
16$80.005/26/2023$14.8912/23/2022
17$85.002/18/2023$16.9912/23/2022
18$70.006/1/2023$6.9912/23/2022
19$70.002/18/2023$9.9912/30/2022 CUSTOM REPORT
20$90.003/18/2023$36.9911/30/2022START DATE:1/1/2022END DATE:5/30/2023
21$85.005/28/2022$4.9911/30/2022RevenueTotal CostProfits
22$10.002/8/2023$25.906/24/20232022$445.00$253.46$191.54
23$10.003/3/2023$12.999/10/20222023$1,000.00$0.00$1,000.00
24$85.001/4/2023$23.979/10/20222024$0.00$0.00$0.00
25$85.002/14/2023$8.997/30/20222025$0.00$0.00$0.00
26$70.004/1/2023$16.987/30/2022Total $$1,445.00$253.46$1,191.54
27$95.001/3/2024$19.787/30/2024
28$95.002/25/2023$4.547/31/2024
29$75.006/26/2023$6.498/1/2024
Sheet1
Cell Formulas
RangeFormula
P22:P25P22=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:Q25Q22=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:R25R22=P22-Q22
P26:R26P26=SUM(P22:P25)
Click to expand...
That works PERFECTLY!!! I can't thank you enough, THANK YOU FOR YOUR HELP.
 
Upvote 0
Does this work for you ?

20231007 SumIfs vs Filter Walker_Ice.xlsx
ABCDEFGHIJKLMNOPQR
1IncomeExpenses
2Income TotalIncome DateExpense _Total $Expense_Date
3854/02/202218.9930/11/2022
410013/03/202316.9930/11/2022
510017/06/202446.9930/11/2024
67025/04/202311.8830/11/2022
79017/06/202332.9930/11/2022
8906/06/20246.991/12/2022
97020/02/202311.858/12/2022
1010028/06/20239.998/12/2023
118527/06/20227.999/12/2023
128017/03/20236.9910/12/2023
13907/06/20237.0911/12/2023
149510/07/20227.9912/12/2023
159531/03/202217.7613/12/2023
168026/05/202314.8923/12/2022
178518/02/202316.9923/12/2022
18701/06/20236.9923/12/2022
197018/02/20239.9930/12/2022 CUSTOM REPORT
209018/03/202336.9930/11/2022START DATE:1/01/2022END DATE:30/05/2023
218528/05/20224.9930/11/2022RevenueTotal CostProfits
22108/02/202325.924/06/20232022445253.46191.54
23103/03/202312.9910/09/20222023100001000
24854/01/202323.9710/09/20222024000
258514/02/20238.9930/07/20222025000
26701/04/202316.9830/07/2022Total $1445253.461191.54
27953/01/202419.7830/07/2024
289525/02/20234.5431/07/2024
297526/06/20236.491/08/2024
Sheet1
Cell Formulas
RangeFormula
P22:P25P22=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:Q25Q22=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:R25R22=P22-Q22
P26:R26P26=SUM(P22:P25)
 
Upvote 0
Alex Blakenburg said:
Does this work for you ?

20231007 SumIfs vs Filter Walker_Ice.xlsx
ABCDEFGHIJKLMNOPQR
1IncomeExpenses
2Income TotalIncome DateExpense _Total $Expense_Date
3854/02/202218.9930/11/2022
410013/03/202316.9930/11/2022
510017/06/202446.9930/11/2024
67025/04/202311.8830/11/2022
79017/06/202332.9930/11/2022
8906/06/20246.991/12/2022
97020/02/202311.858/12/2022
1010028/06/20239.998/12/2023
118527/06/20227.999/12/2023
128017/03/20236.9910/12/2023
13907/06/20237.0911/12/2023
149510/07/20227.9912/12/2023
159531/03/202217.7613/12/2023
168026/05/202314.8923/12/2022
178518/02/202316.9923/12/2022
18701/06/20236.9923/12/2022
197018/02/20239.9930/12/2022 CUSTOM REPORT
209018/03/202336.9930/11/2022START DATE:1/01/2022END DATE:30/05/2023
218528/05/20224.9930/11/2022RevenueTotal CostProfits
22108/02/202325.924/06/20232022445253.46191.54
23103/03/202312.9910/09/20222023100001000
24854/01/202323.9710/09/20222024000
258514/02/20238.9930/07/20222025000
26701/04/202316.9830/07/2022Total $1445253.461191.54
27953/01/202419.7830/07/2024
289525/02/20234.5431/07/2024
297526/06/20236.491/08/2024
Sheet1
Cell Formulas
RangeFormula
P22:P25P22=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:Q25Q22=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:R25R22=P22-Q22
P26:R26P26=SUM(P22:P25)
Click to expand...
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)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

W
  • Locked
  • Question Question
If Statement Help
Replies
1
Views
473
Fluff
Fluff
P
  • Question Question
Filtering by Dates
2
Replies
13
Views
332
RoryA
RoryA
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
D
  • Solved
List of items within a specific month
Replies
4
Views
218
Fluff
Fluff
Y
  • Question Question
IF AND dates calculation
Replies
4
Views
128
yorkam
Y

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top