How do I count a fixed text and unique number in the same cell?

TDobson

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

Paul Newcome

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.

Paul Newcome

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.

TDobson

Thank you for your quick response. I will try that.

TDobson

Thank you I tried that and it worked perfectly.

Paul Newcome

Happy to help. 👍️

TDobson

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?