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.
Best Answer
-
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.
Answers
-
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"))
-
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"))
-
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.
-
-
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.
-
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.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
