Rolling previous 12 month count that spans into previous year.
I have a sheet that is logging customer complaint information. One column is recording the date of the complaint. One a second sheet I have 12 rows, one for current month, and one for each of the previous 12 months. I want that to automatically populate a cell in each of those 12 rows with a count of the total number of complaints.
i.e. this month has a running total of the "current month" May, then next to it is "1 month ago" April, then March, etc. When June begins, it will populate the "current month" field, and May will populate "1 month ago, etc.
I tried:
Current month - 1 (aka last month)
=COUNTIFS({Customer Complaint Log Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1)))))
This seemed to work by adjusting the bold number for each previous month...2 months ago became -2 and -10,2.
It stopped working when the data referenced was from December of 2021, which looked like this (being 5 months ago):
=COUNTIFS({Customer Complaint Log Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 7, 5))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 7, 5)))))
Not sure if it's an issue with the year reference or something else...any ideas are appreciated.
Thanks,
Jim
Best Answer
-
Happy to help. 👍️
Answers
-
I think it is an issue with the YEAR portions. You have +1 even though you are wanting to go back to the previous year. Try swapping it with a -1.
-
Hi Paul,
Thanks for the suggestion.
I tried that change and still get #invalid value. I made the same change to months in 2022 and the formula still works. It's referencing a date on the original sheet that auto populates the date at time of creation, if that makes a difference.
Here's the formula as I tried it this time:
=COUNTIFS({Customer Complaint Log Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - 7, 5))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - 7, 5)))))
-
My apologies. I also missed this piece...
The second MONTH function within each IFERROR should be +. Basically if -5 throws an error then we want to do +7. Whichever way you are going, you want to flip the signs.
=COUNTIFS({Customer Complaint Log Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 7, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 5, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 7, 1)))))
-
Paul,
that fixed it...you're my hero! lol
Thanks a lot for the help!
Jim
-
Happy to help. 👍️
-
Paul - I was looking at this thread and want to check with you how I can get the count of monthly requests for last 1 year from current month. I have used the below formula to calculate the count of tickets. How can I dynamically identify the request counts of current month and the past 12 months? If we are in March 2023, I want the count of requests from April 2022 to March 2023.
=COUNTIFS({Demo - Use Cases for Trial Delivery CompletionMont}, [Completion Month]@row, {Demo - Use Cases for Trial Delivery CompletionYear}, [Completion Year]@row, {Demo - Use Cases for Trial Delivery Optimi Hierarc}, =1, {Demo - Use Cases for Trial Delivery ReqStatus}, OR(@cell = "Completed", @cell = "No Longer Tracking"))
-
@Smartsheet_User I would suggest something like this:
=COUNTIFS({Date Range}, AND(@cell>= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), @cell<= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!