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