COUNTIFS with DISTINCT/COLLECT

Options
Francis PJ P.
Francis PJ P. ✭✭✭
edited 05/07/24 in Formulas and Functions

Hello all,

I've been searching for an hour and I can't seem to find the proper solution. Here's an example :

I need to count the number of DISTINCT Clients if Rep is John and Renewed and Refused are both unchecked. There are several rows for the same client as there are different type but here, we want to count the number of distinct clients only.

The answer to my formulare shoud be 2. John's clients C and E have not renewed nor refused the offer.

What is the proper formula PLEAAASE !

Tags:

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Francis PJ P.,

    I think I figured it out:

    =COUNT(DISTINCT(COLLECT(Client:Client, Client:Client, "C", Rep:Rep, "John", Renewed:Renewed, 0, Refused:Refused, 0)))

    That only counts distinct rows that are Client C and the Rep is John, as well as if Renewed and Refused are unchecked. A few things to note:

    1. You have to make sure the ranges in every part of the formula are the same size (both column-wide and row count), so if you use a range of rows for the Client column (say Client1:Client50), the rest of the ranges that are entered must also use rows 1-50.
    2. Checkboxes are binary so use 1 and 0 as indicators if it's checked (1) or unchecked (0). These are integers, not strings.
    3. I'm not 100% sure if the first range in COLLECT has to be Client:Client, but it seems to work that way so I wouldn't mess with it.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Francis PJ P.
    Francis PJ P. ✭✭✭
    edited 05/07/24
    Options

    @bisaacs Thank you for your reply!

    If I may, I don't need to find how many times Client C only appears in the column, I need to find how many Clients in total does John have that have not renewed nor have refused.

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    @Francis PJ P. Ah got it, then you'd just remove the "Client:Client" ranges, and for the first two ranges in the COLLECT function you'd put Rep:Rep

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    Hi @Francis PJ P.,

    Here is the formula using cross sheet references and looking at all clients.

    =COUNT(DISTINCT(COLLECT({Client}, {Rep}, "John", {Renewed}, 0, {Refused}, 0)))

    Hope this helps,

    Dave

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/07/24
    Options

    Hey @Francis PJ P.

    It took me a moment but I was able to create a formula. I also added an if statement to filter the rows that have checks

    =IF(OR(Renewed@row = 1, Refused@row = 1), "", COUNT(DISTINCT(COLLECT(Client:Client, Rep:Rep, Rep@row, Renewed:Renewed, 0, Refused:Refused, 0))))

    This Formula looks at not only Jon but any rep.

    I hope this helped

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Francis PJ P.
    Options

    I need the formula to be in the sheet summary, would that be the same ?

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    @Francis PJ P.,

    In that case, for a sheet summary, use this.

    =COUNT(DISTINCT(COLLECT(Client:Client, Rep:Rep, "John", Renewed:Renewed, 0, Refused:Refused, 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!