IF and AND Formulas

[Pattatatat]

I already have used a formula to notify me that a job is $20K or more by using =IF(T20>20000,"X", " ") and obviously in T20 is the project amount. I use this becuase I have to track the time for a job $20K or larger

Now, in F20, the cell directly to the left of this formula is the state which the job is in. I no longer have to track jobs in NH or MA, but any other state abbreviation that is also $20K or more will have to be tracked. Is there something to add, so my X onyl appears when the job is $20K or more AND in any state other than NH or MA?

 

[Sancdar]

=IF(AND(T20>20000, F20<>"NH", F20<>"MA"),"X", " ") should work. If you have a job that is exactly $20k this will give a blank, so if that's a concern then you should change the T20>20000 to T20>=20000.
The <> means "not equals" in Excel, so the added bit just makes sure that you don't have either of those two things entered in F20.

 

[Matty]

Welcome to MrExcel.

Try:

=IF(AND(S20<>{"NH","MA"},T20>=20000),"X","")

Matty

 

[Pattatatat]

Man, I was so happy I got such a quick response, but unfortunately my X still shows up in NH and MA jobs over 20K - thanks for the suggestion, I think I may be getting closer

 

[Sancdar]

My formula works fine for me - perhaps you have extra spaces in your state column, or some other oddity? If spaces are the issue then you can use a TRIM(F20).

If you're trying Matty's formula, he fixed the OR/AND confusion but he's using S20 instead of F20 which would give you problems.

 

[Pattatatat]

Ill keep trying to mess with it. Ill let you know how it works out. I did already fix the cell though =)

 

[Matty]

but he's using S20 instead of F20 which would give you problems.

Good spot. Tonight's wine's clearly gone to my head a little quicker than I thought.

Matty

 

[Pattatatat]

Lucky you... its 452pm here and I am still at work. No worries!

 

[Pattatatat]

Thank you. In looking at this again, I realized I probably looked over this quickly and did not realize your formula was slightly different - it worked. Thanks again.