Stumped on an IF Formula

[studiostustar]

Hello everyone, well I've officially broken down and need to ask for help!

Here is the formula I'm trying to acheive in plain English...

If IW5="N", display -4 unless JD19="Y", then display -2 instead. If IW5="Y", display 0. If both IW5 and JD19 equal "Y", then display 0.

Let me know if I need to clarify any further, any help is much appreciated!

[Richard Buttrey]

Hi,

What about

Please Login or Register  to view this content.

Although you've not covered the IW5=Y and JD19=N condition

[studiostustar]

That worked perfectly! Thank you so much!

[studiostustar]

Ok, I've modified my spreadsheet a bit, and now I'm trying to acheive the same thing only this time I'm dealing with ranges. Here's what I'm going for now in plain english...

If G3:I23 contains "ATH", display 0. If G3:I23 contains "WS", display -2. If "ATH" and "WS" both show up in G3:I23, then display 0. If neither "ATH" nor "WS" show up in G3:I23, then display -4.

Sorry if this is a confusing mess, let me know if I need to clarify anything. Thanks!

[FDibbins]

a bit messy, and no doubt Richard will come up with something better, but try this...
=IF(COUNTIF($G$3:$G$23,"ATH")=0,0,IF(COUNTIF($G$3:$G$23,"WS")=0,-2,IF(COUNTIF($G$3:$G$23,"ATH")+COUNTIF($G$3:$G$23,"WS")=0,-4,0)))

[Cutter]

Try:
=IF(COUNTIF(G3:I23,"ATH"),0,IF(COUNTIF(G3:I23,"WS"),-2,-4))

[studiostustar]

Wow, both of those worked great! Thanks again, you all are awesome!

[Cutter]

You're welcome. Glad to help.

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save