COUNTIFS with Jira links and Status
Hello,
I am looking to count the number of open tasks from a smartsheet workflow from JIRA.
I have JIRA links marked 'ITV'= the reference from JIRA (for example ITV-123) and others using TPM-and the JIRA reference. In addition, I want to count all statuses marked as Open, In progress, etc,..
I am trying this formula but doesn't work
=COUNTIFS(JIRA:JIRA, CONTAINS(@cell"ITV"), Status:Status, "In Progress", "Open"))
Any suggestions?
Best Answers
-
Try this...
=COUNTIFS(JIRA:JIRA, CONTAINS("ITV", @cell), Status:Status, OR(@cell = "In Progress", @cell = "Open"))
-
I have added an extra line to show the calculation working.
Hope this helps.
Purnima
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
-
You have your order flipped around backwards in your CONTAINS function.
You have:
CONTAINS(@cell, "ITV")
It should be:
CONTAINS("ITV", @cell)
-
Great! thanks for your help!
Answers
-
Try this...
=COUNTIFS(JIRA:JIRA, CONTAINS("ITV", @cell), Status:Status, OR(@cell = "In Progress", @cell = "Open"))
-
I have added an extra line to show the calculation working.
Hope this helps.
Purnima
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
-
Thanks both! worked well!😀
-
Happy to help! 👍️
-
Guys, could you help again please? In the same sheet as above, I am looking to count tasks by priority. This formula shows 0 items even though I have tasks that match the criteria, any idea why?
=COUNTIFS(JIRA:JIRA, CONTAINS(@cell, "ITV"), Priority:Priority, "P3 - Normal")
-
You have your order flipped around backwards in your CONTAINS function.
You have:
CONTAINS(@cell, "ITV")
It should be:
CONTAINS("ITV", @cell)
-
Great! thanks for your help!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 445 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!