COUNTIFS for Jira data between date range
Hello!
Currently I have a Jira connector that pulls in Jira ticket data and sorts it by ticket topic (column 1), status(Column 2), and date opened. This sheet has a years worth of data separated by date opened.
I know how to use COUNTIFS to find the Open and Closed Jiras by topic (eg - =COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row).
I am trying to add to this formula to only pull in Jira closed ticket information for the month of January 2023.
Any help would be appreciated!
Answers
-
This will do a countifs between two dates. After 12/31/2022 and before 2/1/2023.
=COUNTIFS([Closed Date]:[Closed Date], >DATE(2022, 12, 31), [Closed Date]:[Closed Date], <DATE(2023, 2, 1))
-
Hey @JamesB
Thanks for that reply! So I see that returns the total number of Jira tickets within that date range, is there a way to add a COUNTIFS within this formula to return only CLOSED Jira within that target date range?
So its a COUNTIFS of Closed Jiras AND a COUNTIFS of Jiras within a specific date range.
=COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row) AND COUNTIFS([Closed Date]:[Closed Date], >DATE(2022, 12, 31), [Closed Date]:[Closed Date], <DATE(2023, 2, 1)) ?
I dont think I can use the same function within the formula twice, correct?
-
You should not need an AND statement, just additional criterion in your original formula.
=COUNTIFS({Reference Sheet Name}, "Closed", ({Reference Sheet Name}, >DATE(2022, 12, 31), {Reference Sheet Name}, <DATE(2023, 2, 1),{Reference Sheet Name},Topics@row)
-
I personally have found months are easier to manage when you reference the month and year directly instead of hard-coding in dates.
=COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row, {Closed Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!