Assign cells to groups (ie IF statement) ...

[ryan@csi]

I have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:

=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.

So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.

Your help is greatly appreciated ...

[NBVC]

You need to setup a table on the side listing the states and their corresponding regions..

Then use Vlookup.

e.g.

=Vlookup(B2,$X$2:$Y$51,2,false)

wher X2:Y51 contain the table...

[Brian Matlack]

ryan: You can also use an "OR" statement in your if such as this

=IF(OR(A1="fl",A1="ga",A1="al"),"Southeast",IF(OR(A1="ny",A1="ma",A1="nh"),"Northeast",IF(OR(A1="ca",A1="az",A1="nm"),"Southwest",IF(OR(A1="wa",A1="or",A1="id"),"Northwest",""))))

you can put up to 30 arguments in each "OR" statement so if there are more than 30 states in one of the regions this will not work.

Hope this helps!

[ryan@csi]

can you help me spot the error? please ...

=IF(OR(T20="FL",T20="AL",T20="GA",T20="SC",T20="NC",T20="TN",T20="VA",T20="WV"),"SOUTHEAST",IF(OR(T20="MD",T20="PA",T20="DE",T20="NJ",T20="NY",T20="CT",T20="RI",T20="MA",T20="NH",T20="VT",T20="ME"),"NORTHEAST",IF(OR(T20="TX",T20="LA",T20="MS",T20="AR",T20="MO",T20="OK",T20="KS"),"SOUTH CENTRAL",IF(OR(T20="ND",T20="SD",T20="NE",T20="MN",T20="IA",T20="WI",T20="IL",T20="MI",T20="IN",T20="KY",T20="OH"),"NORTH CENTRAL",IF(OR(T20="WA",T20="OR",T20="CA",T20="NV",T20="ID",T20="MT",T20="UT",T20="AZ",T20="WY",T20="CO",T20="NM",T20="AK",T20="HI","")))))

[NBVC]

You didn't like the Vlookup() table suggestion..much easier to maintain...

...your problem is too many nested functions...Excel 2003 and earlier allow only 7 nested functions...

[ryan@csi]

You didn't like the Vlookup() table suggestion..much easier to maintain...

...your problem is too many nested functions...Excel 2003 and earlier allow only 7 nested functions...

It wasn't that I didn't like it. I actually looked up some info and read up on it. I just didn't fully understand how to set it up.

The cell I need to assign the region to is T20. The table I have set up is in V3:V52 (State) and W3:W52 (Region). How do I set it up?

[NBVC]

What cell are you entering the actual State to look up?

Say it is S20...

Then, in T20 enter formula:

=Vlookup(S20,$V$3:$W$52,2,FALSE)

change S20 to the cell you have the state in.

[ryan@csi]

Now that I have the jobs (and corresponding information) assigned to their respective regions, things get a little more complicated as I need to do 3 more things. Here is the first one ...

1) I need to sum the number of jobs for each region. The catch is that I need to exclude the Wal Mart jobs. This would mean that I need to look in Column H and total all of the jobs that are NOT "WAL-MART STORES, INC". I would also need to look in COLUMN T and sort them by Region.

For the South East Region, I am entering this sum in B25. I'll then do the same thing for the other 4 regions.

How do I do that?

[NBVC]

Close this thread by marking it Solved.

Then start a new thread with your new question!