formula

=COUNTIF({Resolved Date Range}, ="Resolved", IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))


Hi Guys, need some advice- need to create a formula (above) that will count the number of tickets "Resolved" from the top sheet in the ticket status column


and will count the number "Request Date" per week, month, year - to report into bottom table

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    First - I recommend that you create some helper fields for your dates. It makes your formulas a lot easier. In my screenshot, these are the left 2 columns. You can hide them after you set up your formulas.


    Formulas for:

    Sunday this week: =TODAY(1 - WEEKDAY(TODAY()))

    Saturday this week: =TODAY(7 - WEEKDAY(TODAY()))

    Sunday Last week: =TODAY(1 - WEEKDAY(TODAY())) - 7

    Saturday Last Week: =TODAY(7 - WEEKDAY(TODAY())) - 7


    Test Date: =DATE(YEAR(TODAY()), MONTH(TODAY()), 15)

    Last Month: =[Test Date]6 - 30


    About Test Date and Last Month: When you are using formulas looking for data this month, last month, 2 months ago, etc., I find that it's best to use a date in the middle of the month to calculate what last month would be and 2 months ago, etc. The reason for this:

    If I used =Month(today())-1, This formula would work for every month except January - you'll get an error in January when you're looking for the previous month by calculating this month -1 (there is no month 0). By taking a date in the middle of the current month and subtracting 30 days, you'll always get a date in the middle of the previous month, against which you can compare your other dates.


    Now, for your metrics - you're going to refer to these "test date" fields for several of your metrics:

    Year to Date Resolved: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR(TODAY()))

    Issues Resolved Last Month: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR([Test Date]7), [Resolved Date]:[Resolved Date], MONTH(@cell) = MONTH([Test Date]7))

    Issues Resolved This Month: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR(TODAY()), [Resolved Date]:[Resolved Date], MONTH(@cell) = MONTH(TODAY()))

    Issues Resolved Last Week: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], >=[Test Date]3, [Resolved Date]:[Resolved Date], <=[Test Date]4)

    Issues Resolved This Week: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], >=[Test Date]1, [Resolved Date]:[Resolved Date], <=[Test Date]2)

  • Year to Date Resolved: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR(TODAY()))


    I cant seem to get this to work, what am I doing wrong

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!