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

Options

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.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/15/24
    Options

    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
    Options

    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
    KPH ✭✭✭✭✭✭
    edited 02/15/24
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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.