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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!