COUNTM & DISTINCT

Rabia
Rabia ✭✭✭✭
edited 11/16/22 in Formulas and Functions

So I have a source sheet (Permit Intake Form- Virginia), and I’m trying to do a count for the total number of City or Town Permits (Column highlighted below) based on the Unique/Distinct job order number in the column before it. For example: Job Order Number 12345 should have City of Staunton counted once, instead of 3 times for a total City or Town Permit count of 6 (only distinct entries)

I currently have this: =COUNTM({Permit Intake Form- Virginia Range 1})

But it doesn't take the unique entries or the job order number into account. How can I do that?


Answers

  • Samuel Mueller
    Samuel Mueller Overachievers

    @rabia Try this maybe. I'm a little confused on what you are asking:

    =COUNTM(DISTINCT({Permit Intake Form- Virginia Range 1}))

    That will essentially give you the number of different cities or towns selected. So from what I can see in this image your result would be 5 (Staunton, Portsmouth, Harrisonburg, Waynesboro, Smithfield). If you need to separate by job number then you may need to set up a matrix sheet to count things individually.

  • Rabia
    Rabia ✭✭✭✭

    @Samuel Mueller

    Thanks for your suggestion. I'm essentially looking to first review the job type, if it is the same job order number then it should only count duplicate city entries, once.

    Therefore if I look at the total city/township count it would be based on the Job Order Number:

    12345:

    Staunton, Portsmouth, Chesapeake, Harrisonburg, Waynesboro = 5

    1:

    Waynesboro, Smithfield = 2

    So the total city count would yield: 7

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 11/16/22

    @rabia I think this is tricky. I think you are going to have to separate these out. On the sheet where you count you would have a list of counties, "City of Staunton,..." one in each row. Then in the column next to it a formula looking at your source sheet (Multi select drop down column), something like this

    =JOIN(COLLECT({Permit Intake Form- Virginia Range 1(Job Number column)}, {Permit Intake Form- Virginia Range 1(City or town column)}, CONTAINS(County@row, @cell)), CHAR(10))

    Then in a column to the right of that (Job Numbers per County), you have the formula:

    =COUNTM([Unique Job Numbers]@row)

    Then you can sum the numbers in the last column to get unique counties/towns.

    My sample data: So my unique total would be "5"


  • Rabia
    Rabia ✭✭✭✭

    @Samuel Mueller

    Thank you for your suggestion! Trying to make the first part work, but not sure what I'm doing wrong?


  • Samuel Mueller
    Samuel Mueller Overachievers

    @rabia

    What error are you getting as the response?

    Is range1 your counties/towns column?

    the first range in the collect portion should be your job number

    =Join( Collect({JobNumber},{County/Town},Contains(City@row, @cell)), char(10) )

  • Samuel Mueller
    Samuel Mueller Overachievers

    Also you have an extra ) after this range that shouldn't be there


  • Rabia
    Rabia ✭✭✭✭

    @Samuel Mueller

    I've removed the extra )

    The first range is the job order and the second range is the city list.

    I get the following error


  • Samuel Mueller
    Samuel Mueller Overachievers

    @rabia Invalid Ref usually means you mistyped one of the references in your curly brackets { }. Double check that you got those right.

  • Rabia
    Rabia ✭✭✭✭

    Still unable to get it to work. Not sure what I have wrong.

  • Samuel Mueller
    Samuel Mueller Overachievers

    @rabia are you sure on the references? It seemed like earlier that

    was the range of the county/town, not job number. If that's the case you have them switched.

    IF you are still getting the invalid REF then you have typed one of the references in the curly brackets wrong.

  • Rabia
    Rabia ✭✭✭✭

    @Samuel Mueller

    Thank you!! I got the first part to work. I'm stuck with the second part now, which doesn't seem to be counting the unique entries.

    =COUNTM([Unique Job Numbers]@row)


  • Samuel Mueller
    Samuel Mueller Overachievers

    @rabia Change the column "Unique Job Numbers" to a multi select drop down column.

  • Rabia
    Rabia ✭✭✭✭

    @Samuel Mueller

    Thank you so much for all of your help! I was able to change the cell type so I don't technically need the second column anymore


  • Samuel Mueller
    Samuel Mueller Overachievers

    That is true! You could just countM the unique job numbers column if that is all you need. Glad you got it working, have a great rest of your week.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!