Rolling previous 12 month count that spans into previous year.

Jim McWilliams
Jim McWilliams ✭✭
edited 05/16/23 in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Paul Newcome
    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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Jim McWilliams
    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)))))

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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)))))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Jim McWilliams
    Jim McWilliams ✭✭

    Paul,

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

    Thanks a lot for the help!

    Jim

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Smartsheet_User
    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"))

    image.png


    · Share on FacebookShare on Twitter
  • Paul Newcome
    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))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!