Counting Multiple Location Values

Monica Gallegos

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.

Paul Newcome

My apologies. I missed a couple of other issues too. I just saw the extra square bracket and didn't look at anythign else. Try this one...

=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"))

We were also missing a quote and @cell references and had an extra space in there as well.

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

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"))

Monica Gallegos

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

Are you getting an incorrect count or an error?

Paul Newcome

My apologies. I missed a couple of other issues too. I just saw the extra square bracket and didn't look at anythign else. Try this one...

=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"))

We were also missing a quote and @cell references and had an extra space in there as well.

Monica Gallegos

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

Happy to help. 👍️