So basically I have a column in a sheet with single or multiple names separated by a comma (as shown). What formula would I use to tally the total of names without duplicates ?
P.S the names are pulled from elsewhere and we cannot use Drop Downs for the Column
How are you getting the data into this Passenger column? I ask because it looks like potentially there are a number of names that are missing commas between them (like in the first cell).
The easiest way to do this would be to ensure you only have one name per cell, then you can use COUNT(DISTINCT.
The other option would be to use a multi-select helper column (next to this column) which pulls in each name as a separate multi-select value. Then you can JOIN all those cells together into one cell, and count how many names appear in that one summary cell.
We may be able to come up with more ideas if we could learn more about where the source data is coming from and if you're using other formulas right now.
They are pulled in from a PowerAutomate Flow that pulls it from our emails, so we can have it so each name is separated by a comma to assist the formula.
If you have all your names separated by a comma, we can then use the SUBSTITUTE function to translate those names into selections in a multi-select column:
=SUBSTITUTE(Passengers@row, ",", CHAR(10))
Then in your top row (or a different multi-select column) you can JOIN all of these names together:
And use COUNTM to see how many names there are:
Thats fantastic, Thanks very much Genevieve, could I trouble you with 1 more question ?
Due to how our data is imported we get the string n/a in the data, I have played around with Replace and Substitute but cant get it right.
So Basically, Person 1, Person 2, n/a, Person 3, How would I have n/a replaced with a blank space ?
I'm glad to hear it's working for you! 🙂
You can replace "n/a" with a blank space in the first formula, the Substitute one:
=SUBSTITUTE(SUBSTITUTE(Passengers@row, "n/a,", ""), ",", CHAR(10))
FANTASTIC, Thanks very much Genevieve :D
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
I have two Smartsheet's. The First Smartsheet contains all the original data. The Second Smartsheet will contain unique values from one of the columns (the column with a 1 in the IDUn... column). In the Second Smartsheet, I need formulas in the individual column row fields to look up and match the value in the IDUn...…
Hi, I am trying to calculate duration and I have tried every formula I can google leaving me with unparseable or incorrect argument set... the fields I am trying to use in my metric sheet are "Actual Start Date" and then TODAYS date. I currently have several variations of this =IF(TODAY() - [Actual Start Date]@row)) or…
I'm working on a project that distributes free car seats and we collect data from family members for up to 4 children per family depending on their car seat needs. One entry may include 1, 2, 3, and/or 4 children and we ask for specific demographic data per child. Again, all collected in entry so across multiple columns…
©2024. All Rights Reserved Smartsheet Inc.