Counting Distinct with multiple ranges


  • @Lynn Strahm

    Could you please provide a bit more information? What is the rule to use Drawing # vs Quote #? What defines distinct?

    One way to possibly resolve is by adding a column that concatenates the fields (=[Drawing Number]@row + "_" + [Drawing Revision]@row + "_" + [Quote Number]@row + "_" + [Quote Revision]@row)

    Depending on your rules, you may need to add a concatenated column for Drawing and Quote. Either way, the string value will be unique and you can count apply a count formula!

    Let me know if you have any questions!



    Jeff Anderson

    Manager, Business Solutions

    Sevan Technology

    Smartsheet GOLD Partner

  • L_123
    L_123 ✭✭✭✭✭✭

    =count(distinct(collect([site type]:[site type],[drawing number]:[drawing number],"DW0002",[Quote Revision]:[Quote Revision],"A"

    will return all unique values of site type where the drawing number is DW0002 and the quote revision is A

  • Thanks for the responses! What I'm trying to do is write a formula that will tell me how many distinct requests there were by site type. For example, based on the data above, how many distinct "New Equipment" requests are on the list? A specific site may have multiple requests (drawings, quotes or both and revisions) but I want to count that site just once. Below is how the count works out for New Equipment based on the data above:

    DW0001 A, QT0005 A = 1 count

    DW0002 A, QT0006 A & DW0002 B & DW0002 C = 1 count

    QT0003 A & QT0003 B = 1 count

    DW0011 A = 1 count

    DW0013 A, QT0019A = 1 count

    QT0021 A = 1 count

    Total count for New Equipment requests = 6

    Thanks for your help!

  • The above example is the long hand of what I'm trying to write a formula to accomplish. Does anyone have any additional thoughts on how to write a formula to accomplish this? Thanks!

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 09/29/20

    Helper column with an if statement with the result being the number you want associated with the values, then a sumif.

    or if you don't want to use that you can use stacked countif statements with mulipliers.

    Apologies, i missed that you had responded to this.

  • Lynn Strahm
    Lynn Strahm ✭✭
    edited 11/02/20


    I'm struggling to see how a helper column or stacked countif statements will accomplish what I'm trying to do. Maybe example formulas would help me. I thought about a helper column where I join the drawing and quote numbers and then count the distinct in the helper column. That wouldn't cover scenarios where a request included just a quote or drawing and not both. The challenge is writing a formula that recognizes which drawing numbers and quote numbers are for the same same site and counting all of them just once.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!