How to use IF statement formula

jaimedelro

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!

dojones

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(

1. 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)

jaimedelro

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.

dojones

Glad to help