How do I count a fixed text and unique number in the same cell?
![TDobson](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I have a sheet that counts if there is anything in a cell, however to the possibility of a incorrect entry I would like a formula to count if the word Jira is found in the column and if there is a number in the same cell which will be unique in every cell.
=countifs({Range}, "Jira") works for the text how can I also identify that there is a unique number in the same cell.
For Instance
Best Answer
-
If it is always going to be
"Jira unique_number"
Then you should be able to use this:
=COUNT(DISTINCT(COLLECT({Range}, {Range}, CONTAINS("Jira", @cell))))
This will collect how many cells within the range contain "Jira" and then count the distinct/unique entries.
Answers
-
If it is always going to be
"Jira unique_number"
Then you should be able to use this:
=COUNT(DISTINCT(COLLECT({Range}, {Range}, CONTAINS("Jira", @cell))))
This will collect how many cells within the range contain "Jira" and then count the distinct/unique entries.
-
Thank you for your quick response. I will try that.
-
Thank you I tried that and it worked perfectly.
-
Happy to help. 👍️
-
Good day,
I am using this formula to look for distinct values in a column and counting them.
=COUNT(DISTINCT(COLLECT({Mobile Regression Jira's Created}, {Mobile Regression Jira's Created}, <>"")))
I have cells with in the column that have multiple numbers. Does the formula look at each number separately or is it counting them as one?
I does not appear to be seeing them separately judging by the count I receive. Do I need to delineate them with a comma or separate them to their own cells?
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!