# Countifs with Distinct?

Options
edited 12/09/19

I have two columns, Column A and Column B. How can I check Column A for a condition, and then count all the distinct corresponding values in Column B for which that condition is met?

Is there an easy way to do this with Countif / Countifs and Distinct?

Thanks!

Tags:

• ✭✭✭✭✭✭
Options

Can you provide a little more detail? Are you able to provide screenshots with sensitive/confidential information hidden or replaced with "dummy data"?

.

Here is how I see what you are asking for. Obviously this may not be EXACTLY what you are trying to do, but if you could let me know if this is the right or wrong idea, it will help get us started in the right direction.

.

Column A is a Checkbox. Column B is a list of names.

You want to count how many UNIQUE names are in Column B but only in those that have the corresponding box in Column A checked.

.

Is this kind of along the lines of what you are trying to do?

• ✭✭✭✭
Options

Hi Everyone,

I have the same problem. It seems that the DISTINCT() function does not work within a COUNTIFS formula. I only seems to work with COUNT.

So currently I can't figure out how to get a count of unique values that also meet another criteria. For example, I have three columns, Email, Region, Program:

david@abc.com | AMER | Program 1

david@abc.com | AMER | Program 2

I would like something like this:

COUNTIFS(DISTINCT({Email}),{Region},="AMER")

Any ideas?

Thanks.

• ✭✭✭✭✭✭
Options

The DISTINCT function returns a true/false value. It will work in a COUNTIFS so long as you use the proper syntax.

=COUNTIFS([Column Name]:[Column Name], DISTINCT(@cell) = true)

• ✭✭✭✭
Options

Hi Paul,

Thanks for the follow up. Unfortunately that format does not work either.

I am using cross sheet formulas with a helper sheet and it does not work. I also tried it in the source sheet directly as well, and it gives an "#Incorrect Argument Set" error in both cases.

I suspect that it is not possible to use the DISTINCT function within a COUNTIFS unfortunately.

(By the way, the syntax for COUNTIFS is "range, criterion", so the "(@cell)=TRUE" is invalid also. But I tired it anyway).

Thanks,

David

• ✭✭✭✭✭✭
Options

David,

I could have sworn we just recently used DISTINCT within a COUNTIFS as a solution on another post here in the community. After doing further digging I found the post and realized we used COUNT(DISTINCT(COLLECT(.............)))

.

I am not sure though why DISTINCT(@cell) = true cannot be used as a criteria. There are other examples of using similar @cell statements as the criteria once the range was specified that actually worked, but I digress...

.

Anywho...

HERE is a link to the post providing a workaround so that DISTINCT can be used as criteria to count items.

• ✭✭✭✭✭✭
Options

You accidentally pasted something else in the link so here's the correct link: https://community.smartsheet.com/discussion/collect-if-distinct-challenge

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

That was the only other thing I had opened at the time. Not sure how it got messed up. Haha. Thanks for catching that for me.

• ✭✭✭✭✭✭
Options

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Happy to help!

Sorry about the initial confusion. Apparently going on memory (even for recent events) isn't my most reliable skill set. Haha. Glad you were able to get it working.

• ✭✭✭✭
Options

That formula worked perfectly for me as well.

Thanks for sharing!

• Options

The Count, Distinct, Collect combo worked for me as well! THANK YOU!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!