Rolling previous 12 month count that spans into previous year.

Jim McWilliams

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

Paul Newcome

Happy to help. 👍️

Paul Newcome

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.

Jim McWilliams

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)))))

Paul Newcome

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)))))

Jim McWilliams

Paul,

that fixed it...you're my hero! lol

Thanks a lot for the help!

Jim

Paul Newcome

Happy to help. 👍️

Smartsheet_User

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"))

Paul Newcome

@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))