# COUNTM & DISTINCT

Options
✭✭✭✭
edited 11/16/22

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?

• Overachievers
Options

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

• ✭✭✭✭
Options

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

• Overachievers
edited 11/16/22
Options

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

• ✭✭✭✭
Options

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

• Overachievers
Options

What error are you getting as the response?

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

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

• Overachievers
Options

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

• ✭✭✭✭
Options

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

• Overachievers
Options

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

• ✭✭✭✭
Options

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

• Overachievers
Options

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

• ✭✭✭✭
Options

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)

• Overachievers
Options

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

• ✭✭✭✭
Options

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

• Overachievers
Options

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!