Countifs with Distinct?

Jeff S.

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!

Paul Newcome

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?

David Bright

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.

 

 

Paul Newcome

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)

David Bright

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

Paul Newcome

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.

Andrée Starå

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

Paul Newcome

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.

Andrée Starå

David Bright

Thanks Everyone,

Based on the samples provided, I was able to get my scenario solved by using a combination of COUNT, DISTINCT, and COLLECT.

 

It ended up looking like this:

=COUNT(DISTINCT(COLLECT({Email}, {Geo}, ="AMER")))

Thanks.

Paul Newcome

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.

Thiago Castro

@David Bright

That formula worked perfectly for me as well.

Thanks for sharing!

Amyn Aung

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

Btrombler

@Paul Newcome Hi Paul!

I'm trying to use this formula to fix two scenarios

1. For this, I need it to return "18" for the number of distinct requests (by name of Requestor) within a specified date range. I had used a different formula previously, but in the case that the same person requests in the same interval, I will need to correct. For some reason, it's returning "1" at the moment with this formula

=COUNT(DISTINCT(COLLECT([Name of Requestor]:[Name of Requestor], NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))

When I update to this formula, it returns "17"

=COUNT(DISTINCT(COLLECT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))

2)

In this situation, I am utilizing the below to find the # of Divisions FS24 Q4. I need it to count distinct entries in the Divisions column in addition to the Please Specify column. It would be great to ensure it doesn't capture "Other, Please specify" from the Division Column as well (as as not to add extra value to the total count, since we are taking into account the entries in the Plea Specify column), but I haven't figured out that piece.

=COUNT(DISTINCT(COLLECT(Division:Division, [Submission Date]:[Submission Date] <= DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2023, 3, 31)))) + COUNT(DISTINCT(COLLECT([Please specify]:[Please specify], [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))

It's currently returning "3" when it should be 12

Btrombler

Hmm.. After playing around with both I seem to have made progress however for #1 it is still returning "17" when it should be 18. I'm wondering if it's because there are two "Rachels" in the column, even though they have listed a different last name. I did tests on other date ranges outside of this which the formula works for.