Nested If Statement with Multiple Criteria

[small_wonder]

Hi

I need to write an If Statement for a sports picking list that accomodates the following rules:

Display error if:

1 - Term 1 = Rowing + Term 2 = Swimming
2 - Term 1 = Rowing + Term 2 = Swimming + Term 3 = Tennis
3 - Term 1 = Rowing + Term 2 = Swimming + Term 3 = Tennis + Term 4 = Squash
4 - OR Term 1-4 = Goals > 2

My columns are:

C2 (Term 1) D2 (Term 2) E2 (Term3) F2 (Term 4)

How would I go about doing this? I'm sure there is a simple solution, my excel skills are very rusty.

So far I have: =IF(C2="Rowing"+D2="Swimming","Clash",IF(C2="Rowing"+D2="Swimming"+E2="Tennis","Clash",IF(C2="Rowing"+D2="Swimming"+E2="Tennis"+F2="Squash","Clash","")))

But it returns a #VALUE error and is missing criteria 4 from my list.

Thank you

 

[wigi]

Hello

Why do you have conditions 2 and 3 given the content of condition 1?
Condition 1 already rules out conditions 2 and 3.

Also, I do not understand condition 4, sorry.

 

[small_wonder]

I need to be able to pick sports from a list under each term... there are four terms. However, certain sports cannot be picked if another particular sport already exits, e.g. swimming cannot be picked if rowing has already been picked for term 1, etc.

"Rowing" is specific to term 1, "Swimming" to term 2, "Tennis" to term 3 and "Squash" to term 4 (none can be picked if another has been picked.

But "goals" is a sport that can be picked regardless but only once over the four terms.

The error is to to displayed under the offsite column. I have already sorted the rules for onsite and it works. I hope this is clearer.... I think I need more conditions in this case.

Here is a sample of my data:
[TABLE="width: 512"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Tutor Group[/TD]
[TD]Term 1[/TD]
[TD]Term 2[/TD]
[TD]Term 3[/TD]
[TD]Term 4[/TD]
[TD]On-Site[/TD]
[TD]Off-Site[/TD]
[/TR]
[TR]
[TD]Stuart[/TD]
[TD]Dunstan[/TD]
[TD] Rowing [/TD]
[TD] Football [/TD]
[TD] Football [/TD]
[TD] Football [/TD]
[TD] error [/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Dunstan[/TD]
[TD] Rugby [/TD]
[TD] Football [/TD]
[TD] Badminton [/TD]
[TD] Football [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Timothy[/TD]
[TD]Bourne[/TD]
[TD] Badminton [/TD]
[TD] Rugby [/TD]
[TD] Badminton [/TD]
[TD] Rugby [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]Pole[/TD]
[TD] Goals [/TD]
[TD] Fitness [/TD]
[TD] Fitness [/TD]
[TD] Football [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Newman[/TD]
[TD] Fitness [/TD]
[TD] Rugby [/TD]
[TD] Tennis [/TD]
[TD] Badminton [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Pole[/TD]
[TD] Badminton [/TD]
[TD] Badminton [/TD]
[TD] Fitness [/TD]
[TD] Goals [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Roche[/TD]
[TD] Rowing [/TD]
[TD] Badminton [/TD]
[TD] Fitness [/TD]
[TD] Goals [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]

 

[wigi]

For Goals:

=IF(COUNTIF(C2:F2,"Goals")>1,"error","")

For the other conditions, I would set up a list of combinations that cannot occur in the data. Somewhere on a hidden sheet or the like.
Then you can simply look up certain cell values in the list and see if it's in the list or not.
This saves you from coding all the possible conditions into 1 very big IF formula (or several nested IF formulas).

 

[small_wonder]

Thank you WIGI and guys, I think I have resolved the problem. I think by fudging WIGI's advice for the COUNTIF! Apologies for any cross-posting, I'm new, I had no idea!