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:

Name	Tutor Group	Term 1	Term 2	Term 3	Term 4	On-Site	Off-Site
Stuart	Dunstan	Rowing	Football	Football	Football	error	#VALUE!
Edward	Dunstan	Rugby	Football	Badminton	Football
Timothy	Bourne	Badminton	Rugby	Badminton	Rugby
Kevin	Pole	Goals	Fitness	Fitness	Football
David	Newman	Fitness	Rugby	Tennis	Badminton
Peter	Pole	Badminton	Badminton	Fitness	Goals
Adam	Roche	Rowing	Badminton	Fitness	Goals

<tbody>
</tbody><colgroup><col span="8"></colgroup>

 

[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!