Formula to count unique cells, no duplicates, AND total only by an identifier

Cindi Meche

Hi all,

I have three columns.

• One column has a program name (identifier)
• One column has an identification number (100020000)
• One column has a time span (spring, summer, fall, winter)

I only want to count the identification number once and attribute it to the program name, so I am trying to work out a formula:
=COUNTIF(DISTINCT({identification number}), ({program name}), CONTAINS("Program1", @cell))

I'm receiving "INCORRECT ARGUMENT".
What am I getting wrong here?

Thanks for the help,
Cindi

Cindi Meche

Hi, quick update. This part of the formula is working for me to just count the number of times the ID number shows up in the column, without duplicates.
=COUNT(DISTINCT({identification number}))

Here is an example of data, in case it helps. My data is much bigger of course.

I get 5 as a total number of IDs.

So, what I am hoping to do is count the number of ID's that show up for Program1. It should be 4, Program2 should be 5, etc.

Paul Newcome

You need to also incorporate a COLLECT function.

=COUNT(DISTINCT(COLLECT({ID Number}, {Program}, @cell = "Program1", {Time Span}, @cell = "Spring")))

Cindi Meche

Thanks @Paul Newcome. I don't need to add the time span in the equation. With that said, I altered it to:
=COUNT(DISTINCT(COLLECT({ID Number}), ({Program Name}), @cell = "Program1"))

It returns 1, however, =COUNT(DISTINCT({ID Number})) returns 143. It can't be right. Any thoughts?

Paul Newcome

You are doing too much with the parenthesis. Try the syntax I provided using whichever range/criteria set(s) you need without anything extra.

Cindi Meche

Got it!!! Thanks for that, @Paul Newcome. I ended up using:

=COUNT(DISTINCT(COLLECT({ID Number}, {Program Name}, @cell = "Program1")))

Best,
Cindi