How to use IF statement formula
Hi Smartsheet Community,
I currently need help in generating a formula where in I have multiple countries in a dropdown column and I need to identify which region they are from.
Ex: A form collects data from users and they input their country. In the back end (grid), I need to collate all entries per region.
So Country (Japan) should be automatically detected under Region (Asia).
Hope someone can help me generate the formula. There are quite a number of countries so I am unsure how to go with the formula.
Thanks!
Answers
-
There are a number of ways to do this depending on how you want to use the result.
First, create a separate sheet that listed all Countries and Regions, similar to this. Then use Vlookup or Index Match in the main sheet to pull the Region for the Country entered. Then put a formula in the summary sheet to CountIf(
- You can then do a count in the summary sheet like this.
For Asia: =COUNTIF(Region:Region, "Asia")
For Europe: =COUNTIF(Region:Region, "Europe")
2. Create a separate Region Summary sheet and compile data there. If creating a chart, this is better.
Count formula:
=COUNTIF({Country Region Range 1}, Region@row)
-
Thanks for the suggestion!!
I have created a separate grid for the list of all countries and region. Did a vlookup formula as an alternative.
=VLOOKUP(Country@row, {Country / Cluster Range 2}, 2, false)
Things are working as it is.
-
Glad to help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!