I would like to add on to this and count unique values (ie. repeated cells counted oncely once).

Hyo Jung Yoon - 10PM-C

=COUNTIFS({LCS}, $[Primary Column]@row, {Phase}, ISBLANK(@cell))

What can I add to do this?

Hyo Jung Yoon - 10PM-C

How do I I COUNTIF for unique values?

Adrian Backus

https://help.smartsheet.com/function/distinct

Genevieve P.

Hi @Hyo Jung Yoon - 10PM-C

Paul has a good example in this other post, here.

Let me know if this helps! If not, it would be helpful to see a screen capture of your sheet and an explanation of what you're looking to count (but please block out any sensitive data).

Cheers,

Genevieve

Hyo Jung Yoon - 10PM-C

@Genevieve P Thank you! But I am looking to countifs unique values. I am trying to add on to my current formula (=COUNTIFS({LCS}, $[Primary Column]@row, {Phase}, ISBLANK(@cell)) + COUNTIFS({LCO}, [Primary Column]@row, {Phase}, ISBLANK(@cell)) to count the unique values of Project No. Any tips?

Genevieve P.

Hi @Hyo Jung Yoon - 10PM-C

Instead of COUNTIFS, you would want to use COUNT(DISTINCT(COLLECT, as in Paul's formula.

=COUNT(DISTINCT(COLLECT(..............................................)))

In the COLLECT function, you first list the column to be evaluated for the unique values ("Project No."). Then you list each column and criteria afterwards.

For example:

=COUNT(DISTINCT(COLLECT({Project Number}, {LCS}, $[Primary Column]@row, {Phase}, ISBLANK(@cell))))

Then if you want to add 2 COUNTS together, adjust your second COUNTIF statement to also be a COUNT(DISTINCT(COLLECT:

=COUNT(DISTINCT(COLLECT({Project Number}, {LCO}, [Primary Column]@row, {Phase}, ISBLANK(@cell))))

Then you can add together these 2 unique Counts:

=COUNT(DISTINCT(COLLECT({Project Number}, {LCS}, $[Primary Column]@row, {Phase}, ISBLANK(@cell)))) + COUNT(DISTINCT(COLLECT({Project Number}, {LCO}, [Primary Column]@row, {Phase}, ISBLANK(@cell))))

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

You are going to want something more like this...

=COUNT(DISTINCT(COLLECT({Range Containing Unique Values}, {LCS}, @cell = $[Primary Column]@row, {Phase}, @cell = "")))

Hyo Jung Yoon - 10PM-C

Thank you!

Genevieve P.

@Hyo Jung Yoon - 10PM-C

You can use <> to indicate "not" in your logic statements...

ex:

{Phase}, <> "Rexus Activation"

However, in your current formula it's only counting cells that are blank in the Phase column, which will automatically exclude any cells with Rexus Activation.

Paul Newcome

Apparently I should have refreshed my browser before responding. You two had a bit of conversation that I had missed. Haha.

Hyo Jung Yoon - 10PM-C

=COUNT(DISTINCT(COLLECT({Proj. #}, {LCO}, $[Primary Column]@row, {Phase}, PLANNING$1)))

This formula is still counting this project as 3 separate projects. How do I change this formula so that it only counts this once? Above is Proj. #

Genevieve P.

Hi @Hyo Jung Yoon - 10PM-C

You're receiving 3 because those three cells are each a unique value. The formula is looking for an exact match, so as soon as any other information appears after the initial number on the left, it's a new cell value.

Is there a different column you can look at which only includes the first part of the Project Number? Do all of your Project Numbers have the same number of characters to identify them (Ex. 7WA2453 is 7 characters)?

Hyo Jung Yoon - 10PM-C

I see, thank you!