Solved

# IF formula

Posted on 2013-10-11
Medium Priority
207 Views
Experts,

I'm creating a helper column to sort/filter in a report filter section of a pivot table.  There is state column (G) and a tax colum (H).

I want to Say Yes, to all states and when column H = U
but...I want to say Yes when the following states show B in colummn H:
FL, SD, TX, NV, TN, WA, WY

Thanks.
0
Question by:AliciaVee
• 3
• 3
• 2
• +1

LVL 12

Expert Comment

ID: 39567083
Can you try if this formula is what you need?

=IF(H2=U2,"Yes",IF(AND(H2="B",OR(G2="FL",G2="SD",G2="TX",G2="NV",G2="TN",G2="WA",G2="WY")),"Yes"))
0

LVL 38

Expert Comment

ID: 39567100
So you want the helper column (I) to shown Yes when:

column H = "U" or
column H = "B" and column H contains FL or SD or TX or NV or TN or WA or WY

Right?

=IF(OR(H2="U",AND(H2="B",OR(G2="FL",G2="SD",G2="TX",G2="NV",G2="TN",G2="WA",G2="WY"))),"Yes","")
0

LVL 14

Expert Comment

ID: 39567968
The Helper column will always show "YES" while H = U
If H = B, the Helper column will only show Yes for selected states
If H is neither U nor B the Helper column will be blank.
This formula is pasted in row 3 or the Helper column:-
=IF(OR(UPPER(\$H3) = "U",OR(AND(UPPER(\$H3)="B",LEN(\$G3),ISERROR(FIND(UPPER(\$G3),"FL,SD,TX,NV,TN,WA,WY"))=FALSE))),"Yes","")
0

Author Comment

ID: 39569199
Oh Gosh, I don't think I was clear enough.  Sorry...the below is a bit more descriptive.

Column G -- has 2 letter states, example, NJ, NJ, PA..etc

Column J (sorry, added a few more columns) has a tax code, this is either H, or U

I always need to use the H tax code for all states except for the following states:
FL, SD, TX, NV, TN, WA, WY (note, there are multiple tax codes for each state, and it is both U and H)  Actually, all states have U and H -- I need H for most states, and U for the exceptions noted.

The new helper column(where I want it to look at the state column G) should return either a Yes or No.

I tried a few of the examples provided (thanks) but they don't work -- I know its because I was not clear.

Thanks!!
0

LVL 14

Expert Comment

ID: 39569230
So, column G always has only two letters in it indicating a state. Right?
How many characters does column J have?
(a) Only one? - "H" or "U" or "B" or any one of many others?
(b) More than one? - If so, how are they separated?
i - Like "HU" - without any separation?
ii - Like H,U,B,G - with comma separation?
Now, in which case do you want to see "Yes" in the helper column?
(a) If J = "B" And G = [any one of FL, SD, TX, NV, TN, WA, WY]?
(b) If J includes "B" (besides others) And G = [any one of FL, SD, TX, NV, TN, WA, WY]?
0

LVL 38

Expert Comment

ID: 39569408
>> Oh Gosh, I don't think I was clear enough

I such cases, it would be better to post part of your Excel sheet, or create a simple one for this question's purpors. In your sample sheet, you simply type the answers the way you'd like to see them. Can you do that?
0

Author Comment

ID: 39573691
Q:
So, column G always has only two letters in it indicating a state. Right?
A: YES

Q:
How many characters does column J have?
(a) Only one? - "H" or "U" or "B" or any one of many others?
A: YES -- only 1 letter, either H or U (not B...sorry)

Q:
(b) More than one? - If so, how are they separated?
i - Like "HU" - without any separation?
ii - Like H,U,B,G - with comma separation?
A: NO -- only one letter, "H" or "U" -- each State, has multiple lines of H AND U

Q:
Now, in which case do you want to see "Yes" in the helper column?
(a) If J = "B" And G = [any one of FL, SD, TX, NV, TN, WA, WY]?
(b) If J includes "B" (besides others) And G = [any one of FL, SD, TX, NV, TN, WA, WY]?
A:
1. When any of the states (exclude the 7 state exceptions) have the H code, should return YES, (all other rows, where all states will have the U should return NO)
2. When the 7 state exceptions have the U, should return YES, and for the rows where the 7 state exceptions have the H -- should say NO

Thank you!
0

LVL 14

Accepted Solution

Faustulus earned 2000 total points
ID: 39573962
Please try this formula in row 2
=IF(ISERROR(FIND(UPPER(\$G2),"FL,SD,TX,NV,TN,WA,WY")),IF(UPPER(J2)="H","Yes","No"),IF(UPPER(J2)="U","Yes","No"))
0

Author Closing Comment

ID: 39574216
Works GREAT!  Thanks!!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.