Using COUNTM Function to count more than one zipcodes listed in one cell

I have mocked it up for you

This is your sheet with the zip codes in. You need to add a helper column here to find the number of zip codes per cell.

The helper column will calculate the number of zip codes in the row. I suggested counting the characters, dividing by 5 and rounding down, using this formula:

=ROUNDDOWN(LEN([Please record all zip codes that you will service]@row)/5)

Be aware that some symbols, including spaces and commas, are included in the LEN calculation so if you have more than 5 of those, the zip code count will not be accurate.

The somewhere else you can SUM the helper column to get the total.

This could be on the same sheet, in which case you can use this formula:

Or it could be in another sheet, in which case you replace Helper:Helper with a cross sheet reference.

I think I am following what you need and it feels like it is a double look up. The solution that immediately comes to mind is to add a column to the reference sheet to flag any rows that appear in the orange cell. You could do that using an IF HAS formula:

=IF(HAS({Data}, Shortcode@row), 1)

Where Data is the orange cell.

Then in the yellow cell you could put your JOIN COLLECT, returning the shortcodes from your green sheet where the region matches and the IF HAS returns 1.

The formula would look like this:

=JOIN(COLLECT({Region look up shortcode}, {Region look up Region}, "SW Region", {column with IF HAS formula}, 1), CHAR(10))

All those cross sheet references go to the green sheet.

Does that sound like it could work?

All comments

I did suggest removing the second range from the formula:

=COUNTM([Please record all zip codes that you will service]:[Please record all zip codes that you will service])

However, I reread your question and notice that your zip codes aren’t in a multi select column. COUNTM is for multiselect.

So,…

What separates the zip codes within your cell? Are zip codes always 5 digits?

I’m wondering if you could count the separators if these are consistent, possibly adding 1 to each cell if the last zip code is not followed by a separator.

Or count the number of characters in each cell then divide by 5 and round down. So, if zip codes are all 5 digits and the number of separators (that get counted by the LEN function - not all symbols will be counted) in each cell is less than 5 in total, you could add a helper column, use a formula like below in it, and then sum that column.

=ROUNDDOWN(LEN([Please record all zip codes that you will service]@row)/5)

NCNWIncData

Good Afternoon KPH,

To your question all zipcodes will be 5 digits.

=ROUNDDOWN(LEN([Please record all zip codes that you will service]@row)/5)

I created the helper column; however, this formula works for a specific cell and it does not work for the entire column. I attempted to prompt formula to apply to the whole column but I get an INVALID DATA prompt. The issue is I would like to data to be compiled as we receive survey in real time, as oppose to copy the formula over and over to each cell.

Thanks for your thoughts on this,

Jade

Hi Jade

You would need to put the formula in the helper column and calculate the total for each row separately. THEN you can use a SUM formula on the helper column. That would give you the total in real time.

=SUM([Helper Column Name]:[Helper Column Name])

You can make the helper column formula a column formula and hide the column so you don't need to see or think about it. It is just a helper to extract the data from another cell and get it into a format you can do math on.

I have mocked it up for you

This is your sheet with the zip codes in. You need to add a helper column here to find the number of zip codes per cell.

The helper column will calculate the number of zip codes in the row. I suggested counting the characters, dividing by 5 and rounding down, using this formula:

=ROUNDDOWN(LEN([Please record all zip codes that you will service]@row)/5)

Be aware that some symbols, including spaces and commas, are included in the LEN calculation so if you have more than 5 of those, the zip code count will not be accurate.

The somewhere else you can SUM the helper column to get the total.

This could be on the same sheet, in which case you can use this formula:

Or it could be in another sheet, in which case you replace Helper:Helper with a cross sheet reference.

What about the same question, but if I wanted to NOT count duplicates? I have a similar situation where we enter multiple customers in a multiselect dropdown, and want to sum the total number of customers reached (not including duplicates entered on multiple rows). Is there a way to countm for a range, but only distinct? Thank you!

Assuming your customer names are in a normal multi-select and not a Contact type, like this:

Then you can create one cell that joins all the customers from your customer column into one using the formula

=JOIN(customers:customers, CHAR(10))

CHAR(10) is the separator used by multiselect columns.

So, if you make this new column a multiselect dropdown, you will end up with a neat cell with all the customers in. It will look like this:

Then you can use COUNTM on that cell.

Yes, yes yes!! Thank you so much! That did it

Wonderful news! Thanks for letting me know.

Shoot - I thought that was the magic bullet, but am having issues. It seems to be able to work to an extent, but I am guessing the character limit on each cell is preventing it. (Our "Options" are quite long - background is that we use the full and very long names for customers, to enable staff to type any part of the name and select them). This means that the Join cell is reaching the character limit after only a portion of the options are joined there. Do you have any ideas on how I can overcome this? Thank you!

I have been thinking about this. I have one idea that may be feasible.

If your JOIN is exceeding the limit for the cell but you need all your people in one cell to COUNTM them, you could:

create abbreviated versions for all the names, or give everyone a reference number instead of a name.
convert all of the long names to the short version before doing the join.
count the abbreviations.

What do you think?

In practice, this would involve:

Step 1 Create a look up reference table

One column for each of the long names in your drop down (this should be a quick copy/paste) and another for the short version - which could be anything as long as it is unique. Like this:

Step 2 Add a column to the original sheet to return the short names for each selected long name

Add another multi-select drop-down to your original sheet and add a formula to return the short version based on the originally selected longer version. Like this:

I am using the following INDEX COLLECT formula:

=INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 1)

This returns short name from the other sheet where the first long name in the other sheet is selected in the drop-down on the current row. I don't know how familiar you are with functions or cross sheet references so shout if you need an explanation.

I then extended this to work for the 2nd selected option, adding this part in bold:

=INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 1)

+ IF(COUNTM([Original multi-select]@row) > 1, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 2), "")

This means if there is more than 1 thing selected in the original drop down, add a line break (CHAR(10) - which is the separator for multi-select) and then do another INDEX COLLECT, this time returning the second match (that is the 2 at the end).

Then I can repeat this for the 3rd selected option, like this, copying and pasting in the same thing but changing the 1 to a 2 (to mean if there are more than 2 options selected) and the 2 to a 3 (to mean return the 3rd match):

=INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 1)

+ IF(COUNTM([Original multi-select]@row) > 1, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 2), "")

+ IF(COUNTM([Original multi-select]@row) > 2, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 3), "")

And again if there are more than 3 names, to return the 4th:

=INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 1)

+ IF(COUNTM([Original multi-select]@row) > 1, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 2), "")

+ IF(COUNTM([Original multi-select]@row) > 2, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 3), "")

+ IF(COUNTM([Original multi-select]@row) > 3, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 4), "")

You'd need to do this for as many possible things that could be selected.

Step 2b

If this becomes too long in one cell you can create another column and continue the formula there, like this:

=IF(COUNTM([Original multi-select]@row) > 4, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 5), "")

+ IF(COUNTM([Original multi-select]@row) > 5, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 6), "")

+ IF(COUNTM([Original multi-select]@row) > 6, CHAR(10) + INDEX(COLLECT({Short names}, {Long names}, CONTAINS(@cell, [Original multi-select]@row)), 7), "")

Then combine those into one column using

=[Short version]@row + [Short version pt 2]@row

Like this:

Step 3 JOIN all the short versions into one cell and count

Now you can do a JOIN as you had done:

=JOIN([Short version]:[Short version], CHAR(10))

And use COUNTM on that formula.

@KPH Ok, this solution was genius, thought it took me far too long to figure out how to implement. I am so hopeful about the closeness I am to unlocking a lot of data analysis using this solution. I got it to the point where I have a column of joined shortcodes (each is 6 characters long), and some cells in the joined list have up to 60 or so options. Our total list of non-duplicated customers (or in this case, shortcodes), should be around 325. However, when I use the full steps above (the join of all my shortcodes, and then the join of those shortcodes over 1150 rows, followed by CountM for a number, I only come up with 185.

I've tried this in various configurations, and cannot figure out where the 185 (versus the 325 obtained through other means) is coming from. The cell character limit doesn't neccesarily make sense, does it? I Even with the full number of available options that could have been returned in the shortcode list (582 of them), times 6 characters and a space, it would be just slightly over 4000. And truly, we don't have the 582, it should be closer to 325.

Is it possible it is still the character count messing me up in a new way? (For example, is the break between multiselect options more than 1 character? (Is that what the CHAR(10) means?) If so, I could see how it could easily exceed 4000 characters, and thus I would need to repeat this process, but with, perhaps, a 3-digit code instead of 6.

Thank you so much of your patient guidance of this smartsheet newbie!

CHAR(10) means new line. New line is what separates the values in a multi-select drop down in Smartsheet.

Including CHAR(10) in a formula will utilise 8 character spaces of the 4000 character limit.

Are you able to use a subset of your data and check whether that returns the correct answer? That would help identify where the problem could be.

If not, it is definitely worth testing a 3 digit code. If you have less than 1000 customers, a 3 digit code for each will be sufficient. Rather than deleting what you have done, you can add another column next to the 6 digit one that you have and use a formula to make a 3 digit version. Then adjust the other formulas to use the 3 digit column.

Resuming this long and wonderful conversation, @KPH I figured out eventually that I was essentially asking it to do a "join of a join", and getting messed up as cells with multiple shortcodes ended up having "'s around them, and other strange formatting things. I was able to manually do my end of year calculations last year by selecting each joined shortcode column and pulling it into Notepad, removing spaces and "s, and doing analyses in Excel. Worked fine, but took awhile.

This year, your assistance (And finally taking a Smartsheet University course) has further improved my ability to analyze this data by using a =Join(Collect on a new formula sheet with cross-references to return, for example, all of the multiselect options ("Customers") served by different teams or funding sources.

Now I need to somehow use a combination of these 2 solutions to return only shortcodes from certain regions, referenced in another sheet. I am looking for a code for the yellow cells below, which would pull from the orange cell to return only shortcodes that belong to a certain region, which is listed in a separate reference sheet (example of what it looks like in green).

I feel like I have all of the tools to do this, but am getting caught up in the formula and would love an assist. Thank you so much!

=IF(HAS({Data}, Shortcode@row), 1)

Where Data is the orange cell.

Then in the yellow cell you could put your JOIN COLLECT, returning the shortcodes from your green sheet where the region matches and the IF HAS returns 1.

The formula would look like this:

=JOIN(COLLECT({Region look up shortcode}, {Region look up Region}, "SW Region", {column with IF HAS formula}, 1), CHAR(10))

All those cross sheet references go to the green sheet.

Does that sound like it could work?