Countifs with Distinct?

Jeff S.
Jeff S.
edited 12/09/19 in Formulas and Functions

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:

Comments

  • Paul Newcome
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • David Bright
    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
    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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • David Bright
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    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

    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.

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    yescool

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thiago Castro
    Thiago Castro ✭✭✭✭

    @David Bright

    That formula worked perfectly for me as well.

    Thanks for sharing!

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

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

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!