IF formula

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.
AliciaVeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
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
Gerwin Jansen, EE MVETopic Advisor Commented:
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","")

Open in new window

0
FaustulusCommented:
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","")

Open in new window

0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

AliciaVeeAuthor Commented:
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
FaustulusCommented:
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
Gerwin Jansen, EE MVETopic Advisor Commented:
>> 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
AliciaVeeAuthor Commented:
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
FaustulusCommented:
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"))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AliciaVeeAuthor Commented:
Works GREAT!  Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.