Alternative to COUNTIF with HAS Formula

Barbara Silva
Barbara Silva ✭✭
edited 07/07/22 in Formulas and Functions

I need help, please :)

On a support sheet, I have a list of Zip codes and the areas of the city they correspond to. On my main sheet, I have a "Company HQ Address" column, that contains the Zip code, but NOT on its own column, it's listed within the cell containing the full address. When I use "=COUNTIF({Source Sheet Range 2}, HAS(@cell, "89148")) for example; it only counts it if the cell on the address column lists the Zip code without additional data, which won't work for me.

Ultimately, I want to count the number of rows/records I have per area of the City (North West, North Central, North East, South West, South East). Any ideas on how to fix my formula above, or any other/better way to do this that I am unaware of?

Thank you so much in advance!


Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 07/08/22 Answer ✓

    @Barbara Silva

    Hi Barbara. HAS requires an exact match. Switch to using CONTAINS instead.

    =COUNTIF({Source Sheet Range 2}, CONTAINS("89148", @cell))

    CONTAINS is formatted a little differently so you'll need to put what to search for before where to search at.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!