Count with distinct problems
I am trying to count the number of unique numbers referencing a column in a separate Smartsheet. It returns the number '1' which I know is incorrect.
The formula I used is:
=COUNT(DISTINCT({Study number}))
The column referenced does have nested values, could that be part of the problem? Or is there a row limit? The Smartsheet that I am referencing does have 1600+ rows. I have used other formulas with this Smartsheet and never run into any issues.
I would like to expand on this to include other criteria, but the COUNTIFS function does not work with DISTINCT as I have read in other columns. Not sure how to go about expanding this formula to work.
My ultimate goal would be to get:
Number of unique study numbers that also match the following criteria:
COUNTIFS:
Specific Supervisor - {Supervisor}, FIND("XXX", @cell) > 0
Review number is not blank - {Review Number}, <>""
Fall within a specific review date range - {Review Date}, AND(@cell >= DATE(2021,1,1), @cell <= DATE(2030,1,1))
Best Answer
-
You forgot your second DATE function...
=COUNT(DISTINCT(COLLECT({Study Number}, {Supervisor}, FIND("XXX", @cell) > 0, {Date Range Reviewed}, <>"", {QC Date}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2030, 1, 1)))))
Answers
-
For the initial issue... How are those numbers being populated? Are they a mix of data types such as numbers and text in some cells, all numbers in other cells, and all text in other cells or some kind of combination of those?
To add criteria, you would use a COLLECT function.
=COUNT(DISTINCT(COLLECT({Range to count based on being distinct}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, .........................)))
-
The numbers are being typed in, majority are just numbers (20123456) and 11 are numbers with letters in front (XXX000123).
-
There's the problem then. All data must be of the same data type. So you have some that are number strings and others that are text strings.
Insert a text/number helper column that can be hidden from view after setting up and use this column formula (update column name to whatever is in your sheet that is housing the data you want to count):
=[Column Name]@row + ""
This will convert everything including the numbers into text strings. Now that it is all the same data type, you can reference this new helper column in your COUNT/DISTINCT formula.
-
There are always new rows being added. Will I have to input the formula for each new row so that it converts it to text? I tried to link the column to a new sheet, but it says you can only link 500 at a time and since my number of rows currently exceeds that I'm not sure how to go about doing that.
-
Okay, so I figured out that problem. Now my formula is returning "UNPARSEABLE".
=COUNT(DISTINCT(COLLECT({Study Number}, {Supervisor}, FIND("XXX", @cell) > 0, {Date Range Reviewed}, <>"", {QC Date}, AND(@cell >= DATE(2021, 1, 1), @cell <= (2030, 1, 1)))))
-
You forgot your second DATE function...
=COUNT(DISTINCT(COLLECT({Study Number}, {Supervisor}, FIND("XXX", @cell) > 0, {Date Range Reviewed}, <>"", {QC Date}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2030, 1, 1)))))
-
There's the problem then. All data must be of the same data type. So you have some that are number strings and others that are text strings.
Insert a text/number helper column that can be hidden from view after setting up and use this column formula (update column name to whatever is in your sheet that is housing the data you want to count):
=[Column Name]@row + ""
This will convert everything including the numbers into text strings. Now that it is all the same data type, you can reference this new helper column in your COUNT/DISTINCT formula.
Is there a way to incorporate this (=[Column Name]@row + "") into the count/distinct formula? I have over 300 columns in this sheet (from copy row from other sheets) and trying to avoid adding more columns to it. I'm trying to create a cross-sheet formula that will count the distinct number in the column with criteria.
Thank you!!
-
@Christina Lam I believe it might be possible. Are you able to provide the formula you currently have for reference so I can get my head back into this one?
-
Thanks for getting back to me.
=COUNT(DISTINCT(COLLECT({People #}, {Job Orders Status}, ="Active")))
{People #} is a column with numbers and once in a while there will be symbols like /
Appreciate your help!
-
@Christina Lam In this case you will need the helper column on the source sheet to convert all entries in the {People #} range to text values.
-
That's too bad. Thanks for trying!
Help Article Resources
Categories
Check out the Formula Handbook template!