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

NCNWIncData

Good Evening All,

I am collecting zipcodes in one column, a person could list more than one zipcode in a cell. I would like to total the number of zipcodes as the survey responses come in real time. Its okay if I count duplicates as well.

This is a survey so I am using the text/number format.

Issue: COUNTM does not count all values in listed in the cell, it counts one value per cell.

Current formula: =COUNTM([Please record all zip codes that you will service]:[Please record all zip codes that you will service],(@cell))

Thanks for the support.

KPH

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

KPH

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.

KPH

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.

mhawkins

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!

KPH

Hi @mhawkins

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.

mhawkins

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

KPH

Wonderful news! Thanks for letting me know.

mhawkins

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!

KPH

Hi @mhawkins

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:

1. create abbreviated versions for all the names, or give everyone a reference number instead of a name.
2. convert all of the long names to the short version before doing the join.
3. 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.

mhawkins

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