Can you use Distinct() with Countifs()?

MCorbin

I'm trying to create formula that counts the unique objects in a workspace (I upload the "Access" sheet from User Admin into a Smartsheet).

My formula:

=COUNTIFS(DISTINCT({WS1 Key}, <>"", {WS1 Workspace}, Workspace@row))

It's coming back as incorrect argument. I've tried moving the close parenthesis around like this:

=COUNTIFS(DISTINCT({WS1 Key}), <>"", {WS1 Workspace}, Workspace@row), but it's still not working.

The source sheet is the Access sheet. The formula is housed in a sheet that lists every workspace on my account (workspace@row) along with the owner of each workspace (and whether or not I have it scheduled for backup, but that's not relevant)

I'm guessing I might be able to come up with some kind of combination of collect and count, but haven't figured it out yet....

Any suggestions?

Paul Newcome

You would want to use

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

Paul Newcome

The COLLECT function gives you the ability to specify range/criteria sets. That's what lets you specify workspace, key, etc.

Paul Newcome

You would want to use

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

MCorbin

Thanks!

Paul Newcome

The COLLECT function gives you the ability to specify range/criteria sets. That's what lets you specify workspace, key, etc.

firestorm

Hello,

=COUNT(DISTINCT(COLLECT({Screening Form Name}, {Screening Form Location}, [Primary Column]@row, {Screening Form Date}, TODAY(), {Screening Form Session}, Session@row)))

Does the DISTINCT capture the unique value in the first criteria, i.e. Name, or does it apply to all the other criteria?

I have also created a dashboard to show numbers which are calculated from the COUNT(DISTINCT(COLLECT formula, the dashboard always tend to show the initial COUNT numbers before the unique values are calculated. I have to open up the sheet to refresh before the dashboard would show the final accurate numbers.

Any advice?

Paul Newcome

The DISTINCT will filter out the results of the COLLECT. The COLLECT is only pulling the first range based on the ranges/criteria that follow.

So working from the inside out, you would first COLLECT all cells within the {Screening Form Name} range based on the ranges/criteria remaining. Now that you have pulled based on criteria using the COLLECT, you wrap it in the DISTINCT function to remove duplicates from those results. Now that you have your DISTINCT list, you wrap it in a COUNT function to find out how many are there.

Emily G

Hi there - still having a little problem with the combo of DISTINCT and COUNTIFS.

Situation: I've got a sheet with a lot of projects (which we call "use title"), some of which are funded with different funding sources and each row identifies a unique use/funding source combination.

Problem: I am trying to get a sheet summary count of each unique project (i.e, distinct use title, regardless of funding source), which has a corresponding status of "Approved". See my function below, what am I doing wrong?

=COUNTIFS(([Approval Status]:[Approval Status],"Approved"), ([Use Tile]:[Use Title],DISTINCT(COLLECT([Use Tile]:[Use Title]))))

Appreciate any help you can provide!

Genevieve P.

Hi @Emily G

You can add your first criteria (the Approval Status) inside of the COLLECT function to narrow down your search. Try the same structure @Paul Newcome has written above:

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

Like so:

=COUNT(DISTINCT(COLLECT([Use Tile]:[Use Title], [Approval Status]:[Approval Status], "Approved")))

Cheers!

Genevieve

Paul Forrest

Hi, I am having a similar issue and cant seem to get the Count Distinct Collect to work.

I am working on a calculation sheet to feed my dashboard. What I am looking to achieve is to have data that says

Region EMEA has X number of customers that plan to transition.

To get this I would expect it to look at region column, find EMEA, then look at are you planning to transition find yes and lastly look at account name and count distinct values as there is multiple lines per customer.

Can anyone help please as I am lost on this one, thanks

Genevieve P.

Hi @Paul Forrest

Try something like this:

=COUNT(DISTINCT(COLLECT([Account Name]:[Account Name], Region:Region, "EMEA", [Are you planning to transiton to 16G]:[Are you planning to transiton to 16G], "Yes")

The first range you list is the one you want to count distinct values in. Then you list each column and value to search for afterwards, like a filter.

Let me know if that works for you!
Cheers,
Genevieve

Paul Forrest

https://community.smartsheet.com/discussion/comment/363354#Comment_363354

@Genevieve P. This is perfect, now I get the unique count needs to be first 😁

Thank you so much its all working now

Genevieve P.

No problem! I'm glad I could help 🙂