COUNTM & DISTINCT

Rabia

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?

Samuel Mueller

@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

@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

@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

@Samuel Mueller

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

Samuel Mueller

@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

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

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

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

Rabia

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

Samuel Mueller

@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

@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

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

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

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.