need help adding year to a SUMIF formula.
I have a cross sheet formula that sums activities based on "area" and displays in a dashboard.
=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="West")
This formula is currently working. I need this same calculation to perform based on start date year. Currently, all the start dates are after 01/01/2022. Once we begin adding 2023, these are the only dates I want in the calculations to be used for a dashboard.
Here is what I have tried:
=SUMIFS(Start Date:Start Date, Year:Year, 2023, {ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central") - receive UNPARSABLE
=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date} this formula returns as #INVALID REF
Thanks,
Answers
-
You would use something like this:
=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date}, IFERROR(YEAR(@cell), 0) = 2023)
-
thanks for your assistance. However, I am receiving this error:
-
I don't see an error in your screenshot.
-
=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date}, IFERROR(YEAR(@cell), 0) = 2023)
-
You will need to follow the appropriate steps to create the cross sheet reference (the same way the other cross sheet references were made)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!