Nested IF with multiple criteria

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.

Requirements:

1 "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).

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

3 The error is to to displayed under the offsite column. I have already sorted the IF statement rules for onsite and it works. e.g the first entry should be an error. I hope this is clear.

How would I go about doing this? Would I need a giant IF?

I have attached a sample.

Hi small_wonder,

Welcome to the forum.

The error is to to displayed under the offsite column

I have checked you have a working formula in G2 ... then what you need in H2 and how different will be that from G2 ?

Regards,
DILIPandey

<click on below 'star' if this helps>

*whoo hoo* Thank you Dennis

Almost there, the conditions for G2 are that Football, Fitness, Badminton and Rugby cannot be selected more than twice over four terms (C4:F4). I have sorted that as they fall under "on-site" sports.

I have got the H2 formula working for "off-site" based on your help (the conditions are below).

Now my question is how do I add the COUNTIF for Goals to the formula.. condition 10? It's the same as G2 but I dont understand how to incorporate it. Also can you please write down how it all works in English or Laymans term, e.g. the logic of OR/AND, the brackets,etc? I'm really rusty and I would like to improve.

=IF(OR(AND(C2="Rowing",D2="Swimming"),AND(C2="Rowing",D2="Swimming",E2="Tennis"),AND(C2="Rowing",D1="Swimming",E2="Tennis",F2="Squash"),AND(C2="Rowing",E2="Tennis"),AND(C2="Rowing",F2="Squash"),AND(D2="Swimming",E2="Tennis"),AND(D2="Swimming",E2="Tennis",F2="Squash"),AND(D2="Swimming",F2="Squash"),AND(E2="Tennis",F2="Squash")),"Clash","")


Conditions:

1. IF C2=Rowing + D2=Swimming
2. IF C2=Rowing + D2=Swimming + E2=Tennis
3. IF C2=Rowing + D2=Swimming + E2=Tennis + F2=Squash
4. IF C2=Rowing + E2=Tennis
5. IF C2=Rowing + F2=Squash

6. IF D2=Swimming + E2=Tennis
7. IF D2=Swimming + E2=Tennis + F2=Squash
8. IF D2=Swimming + F2=Squash

9. IF E2=Tennis + F2=Squash

10. OR if Goals >1 over C4:F4

We would love to continue to help you with your query, but first, before we can proceed, please see the forum rules and cross posts and adjust accordingly...

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

My apologies Jeff, but my problem had not been solved until here. This is the closest I have gotten. I also wouldn't have cross posted if I had a solution. I did not intentionally post to see who would reply first. I genuinely needed an answer asap as I am rubbish at excel and I was not getting the solution that I required. I have also spent this weekend and last weekend trawling through existing solutions which did not cater for my needs. I do not appreciate a scawlding as if I was a a 5 year old.

I posted here to talk to experts who seemingly were able to solve my issue in 5 minutes flat instead of spending a double weekend dreaming of brackets and logic. I have asked the TWO other sites to remove my thread.

Regards

SW

PS I still have a problem

Before we can proceed with this thread, please comply with post #6.

We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post.