Counting Multiple Location Values

I have a metric sheet and I'm currently using a CountIF formula to count the locations in my sheet, works great. But I'd like to take it a step further and I want to group the location entries into APAC, NA, Canada, & EMEA.

I'm trying to use this formula:

=COUNTIF({ERG MEMBERSHIP EA Location}, "Hyderabad, India", "Madhapur, Hyderabad", "Seoul, Korea", "Shanghai, China", "Singapore, Singapore", Sydney, Australia", "Tokyo, Japan")

And I tried it with the brackets [ ].

=COUNTIF({ERG MEMBERSHIP EA Location}, "Hyderabad, India", ["Madhapur, Hyderabad", "Seoul, Korea", "Shanghai, China", "Singapore, Singapore", Sydney, Australia", "Tokyo, Japan"])

Neither works, and I've revisited the CountIF page and I'm not able to track down what I'm missing. Any suggestions? Screen grab below.



Tags:

Best Answer

Answers

  • RossL
    RossL ✭✭✭✭✭✭

    @Monica Gallegos

    you can do this with an IF OR, if you are alredy using count ifs for each area below you can just do a Sum fromula for all the countIF formulas you are already using

    =CountIF({ERG MEMBERSHIP EA Location}, OR ("Hyderabad, India", ["Madhapur, Hyderabad", "Seoul, Korea", "Shanghai, China", "Singapore, Singapore", Sydney, Australia", "Tokyo, Japan"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Left one of the square brackets in. Here it is removed...

    =COUNTIFS({ERG MEMBERSHIP EA Location}, OR ("Hyderabad, India", "Madhapur, Hyderabad", "Seoul, Korea", "Shanghai, China", "Singapore, Singapore", Sydney, Australia", "Tokyo, Japan"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • It's not working as expected, when i use the corrected formula...

    =COUNTIFS({ERG MEMBERSHIP EA Location}, OR ("Hyderabad, India", "Madhapur, Hyderabad", "Seoul, Korea", "Shanghai, China", "Singapore, Singapore", Sydney, Australia", "Tokyo, Japan"))

    I like the idea of summing existing values as my back up. But would certainly like to at least figure out how to count three values.

    The field name is: "ERG MEMBERSHIP EA Location"

    Values in the single drop down contain:

    "Hyderabad, India"

    "Madhapur, Hyderabad"

    "Seoul, Korea"

    "Shanghai, China"

    etc...

    Any additional thoughts on why that formula isn't producing as expected. I have to admit I'm kind of thrown off on the order of operation between the field name and OR.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you getting an incorrect count or an error?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • It took me a few day's to dig into this, but wanted to let you know it did work. Using the latest formula approach with @cell = . I did have to take it step by step and re-write, there must have been something within the formula that I had wrong.

    =COUNTIFS({ERG MEMBERSHIP EA Location}, OR(@cell = "Hyderabad, India", @cell = "Madhapur, Hyderabad", @cell = "Seoul, Korea", @cell = "Shanghai, China", @cell = "Singapore, Singapore", @cell = "Sydney, Australia", "Tokyo, Japan"))

    Thanks so much for the info in this community, it's proven helpful time and time again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!