SUMIF Previous Month, Current Year
Hello,
I am trying to use the Sheet Summary feature to calculate [Hours worked] for the previous month based on a date column [Service End Date]. I have the following formula which is giving me a much larger value than expected and I believe this is due to having multiple years of data and only looking at the month it is pulling data from the previous month of several years. I have not been able to figure out how to add a criteria for current year.
=SUMIF([Service End Date]:[Service End Date], MONTH(@cell) = MONTH(TODAY() - 1), [Hours worked]:[Hours worked])
I have read several other posts and solutions but am not having any luck. Thanks in advance!
Best Answer
-
Try this...
=SUMIFS([Hours worked]:[Hours worked], [Service End Date]:[Service End Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))
Answers
-
Try this...
=SUMIFS([Hours worked]:[Hours worked], [Service End Date]:[Service End Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))
-
That worked perfectly Paul! I appreciate your time.
-
Happy to help. 👍️
-
I modified this formula to look forward a few months a but once we get into the next year (2022) the result is 0. I've tried several ways but get the same 0 result. What am I missing. Thanks in advance
!
=SUMIFS([Man Days]:[Man Days], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) + 2), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))) * 8
-
Hey, @Paul Newcome, is there a way to add another provision to your formula here? I'm wanting to only sum the values from last month where "Northeast" is in the column labeled "Region." I've tried adding another AND formula but my syntax must be off.
=SUMIFS([Referred Out]:[Referred Out], [Date Only]:[Date Only], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()), AND(Region = "Northeast"))))
-
@Nate H You would need to continue with the appropriate syntax of range comma criteria.
=SUMIFS([Referred Out]:[Referred Out], [Date Only]:[Date Only], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))), Region:Region, @cell = "Northeast")
-
Thanks, sir. @Paul Newcome
-
@Nate H Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!