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

Options

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

What can I add to do this?

• Options

How do I I COUNTIF for unique values?

• ✭✭✭✭
Options
• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

@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?

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

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

• edited 04/22/21
Options

Thank you!

• Employee
Options

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.

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

• Options

=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. #

• Employee
Options

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)?