COUNTM & DISTINCT

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

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

Waynesboro, Smithfield = 2

So the total city count would yield: 7

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

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

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

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

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

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

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

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

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)

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

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