Need help counting the number of unique values in a cell while referencing a second range

Jen O

Hi!

I have a dashboard which contains multiple rows with various study numbers (listed in a Study Number column). Some of the study numbers occur more than once in the column (for example study 12345 is for liquid samples and in another row, study 12345 is for solid samples). Each study number is also associated with a study status (ACTIVE, CLOSED) which appears in another column. I'm trying to

1. Count each study number only once no matter how many times it appears in the column
2. Define the status for the study since it should be the same no matter how many times it appears.

For example, in the following example, my macro will ultimately tell me that I have 2 active studies. Just to note, my column that has study number has ~200 rows in reality and the study numbers don't always appear next to each other in order.

Thank you!

STUDY Sample Type. STATUS

ABCDE. solid active

ABCDE. liquid active

CCCCC. liquid active

CCCCC solid active

Genevieve P.

Hi @Jen O

Thank you for the images, this definitely helps. You can use my second formula example for this!

=COUNT(DISTINCT(COLLECT({Column with Project}, {Status Column}, "ACTIVE")))

Formula Explanation

The COLLECT function essentially creates a filter. You first list the range you want to gather (in this case, the Project name). Then you list a column that has criteria (the Status column) and what that criteria is for the filter (that the Status is "ACTIVE").

COLLECT({Column with Project}, {Status Column}, "ACTIVE")

Therefore, the COLLECT function will only bring back the values from the Project column that have ACTIVE in the Status column.

Then the DISTINCT function creates another filter layer, only looking at the unique values from this filtered Project column list.

DISTINCT(COLLECT(filtered values)

Finally, the COUNT counts how many Distinct values there are in that collected, filtered list. This enables the formula to output a number. Does that make sense?

COUNT(DISTINCT(COLLECT(filtered values)

Cross Sheet Ranges

To create the reference {in these}, click on the "Reference Another Sheet" link in the formula helper pop-up window like so:

Then search for your source sheet and click on the column name you want to reference. I put in helper text so you know which one should be selected.

You can learn about creating Cross Sheet References in this Help Article.

You may also want to review the Webinars on Formulas, here.

Let me know if this works for you or if you still have questions and I'll be happy to explain further.

Cheers,

Genevieve

Jen O

I forgot to mention that I'm doing the calculation in one sheet and referencing the dashboard sheet.

Bassam Khalil

Hi @Jen O

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Genevieve P.

Hi @Jen O

You can use a COUNT(DISTINCT formula to count the number of distinct values in a column!

Try this:

=COUNT(DISTINCT({Column with Study Number}))

If you only need to find the active numbers, try this:

=COUNT(DISTINCT(COLLECT({Column with Study Number}, {Status Column}, "Active")))

Let me know if this works for you! If not, it would be helpful to see a screen capture of your set-up, but please block out sensitive data.

Cheers,

Genevieve

Jen O

Thanks All: I'm attaching the PowerPoint so I can be more explicit in what I'm trying to do. Apologies in advance if you have tried answering this.

SMARTSHEET HELP.jpg

Genevieve P.

Hi @Jen O

Thank you for the images, this definitely helps. You can use my second formula example for this!

=COUNT(DISTINCT(COLLECT({Column with Project}, {Status Column}, "ACTIVE")))

Formula Explanation

The COLLECT function essentially creates a filter. You first list the range you want to gather (in this case, the Project name). Then you list a column that has criteria (the Status column) and what that criteria is for the filter (that the Status is "ACTIVE").

COLLECT({Column with Project}, {Status Column}, "ACTIVE")

Therefore, the COLLECT function will only bring back the values from the Project column that have ACTIVE in the Status column.

Then the DISTINCT function creates another filter layer, only looking at the unique values from this filtered Project column list.

DISTINCT(COLLECT(filtered values)

Finally, the COUNT counts how many Distinct values there are in that collected, filtered list. This enables the formula to output a number. Does that make sense?

COUNT(DISTINCT(COLLECT(filtered values)

Cross Sheet Ranges

To create the reference {in these}, click on the "Reference Another Sheet" link in the formula helper pop-up window like so:

Then search for your source sheet and click on the column name you want to reference. I put in helper text so you know which one should be selected.

You can learn about creating Cross Sheet References in this Help Article.

You may also want to review the Webinars on Formulas, here.

Let me know if this works for you or if you still have questions and I'll be happy to explain further.

Cheers,

Genevieve

Jen O

Thank you so much Genevieve for the clear explanation on everything. It worked!!!

Genevieve P.

Wonderful! I'm so glad to hear it worked for you. 😊

bhsiao_regcorp

I have a formula that works within a sheet but not when I reference the sheet from an external sheet. I am trying to count the number of unique order numbers.

In the sheet this works: =COUNT(DISTINCT({Daily Sales Tracking Range 1})) . It returns a quantity of 15

If I use it in the external sheet like: =COUNT(DISTINCT({Daily Sales Tracking Range 1})) . This returns a quantity of 1.

Genevieve P.

Hi @bhsiao_regcorp

Can you post a screen capture of what the column looks like in that other sheet? (But block out sensitive data)

bhsiao_regcorp

Genevieve P.

Hi @bhsiao_regcorp

Thank you for this image! When I select a column that has numbers in it like this in my cross-sheet reference window, the DISTINCT function is able to correctly identify the individual values.

Is it possible that the reference is only pointing to 1 cell instead of the whole column?

Check your {Daily Sales Tracking Range 1} to ensure it's highlighting the full column. Does it look like this?

bhsiao_regcorp

Yes, it does

Genevieve P.

Hi @bhsiao_regcorp

Can you post a screen capture of the formula as you have it written in Smartsheet?

bhsiao_regcorp

Genevieve P.

Hey @bhsiao_regcorp

I'm unable to see why your formula is only finding 1 unique value, if you've selected the entire column as your range.

Can you try adding in new criteria in case it's being confused by blank cells:

=COUNT(DISTINCT(COLLECT({Daily Sales Tracking Range 1}, {Daily Sales Tracking Range 1}, @cell <> "")))

This just says that the cell should not be blank to be considered in your formula. Let me know if this works!

Cheers,

Genevieve