If Statement Help

[MrNerd]

I have 3 cells (A,B,C) I am looking pull data from to produce an output. All of the formulas/outputs needs to be within 1 cell/formula to make this work. I am really sorry for the structure I have put this in. I am not sure how else to express it. In the end, I am not sure if this is a IF statement or what.

A (A could be any one of these 5 clients)
Client 1
Client 2
Client 3
Client 4
Client 5

B (B could be any one of these 5 conditions)
Condition 1
Condition 2
Condition 3
Condition 4
Condition 5

C (C could be any one of these 3 options)
Option 1
Option 2
Option 2

Formulas and Output (all of this would need to be included in 1 formula so that any of the outputs will populate)

Client 1 + (Condition 1 OR Condition 2 OR Condition 3) + (Option 1 OR Option 2) = 15

(Client 2 OR Client 3 OR Client 4 OR Client 5) + (Condition 1 OR Condition 2 OR Condition 3) + (Option 1 OR Option 2) = 1

Condition 4 + (Option 1 OR Option 2) = 8

Condition 5 + (Option 2 OR Option 2) = 16

Option 2 = 1000

 

[kweaver]

It would make more sense if you illustrated your sample data and expect results using XL2BB, please.
Also, please change your profile to indicate which version of Excel you are running.

 

[MrNerd]

So sorry, my company does not let me download anything onto my computer unless preapproved.

Here is a screen cap of what it may look like in the spreadsheet. Each of those formulas would be run in a single cell in column E to produce the output in the original post. Again, sorry.

 

[kweaver]

That's a start but what is the output supposed to be?

 

[MrNerd]

Each of the below statements is what would dictate the outputs. Outputs are bold. Basically, I am looking for one long IF statement that would check ALL the below in a single cell and when one of those formulas is met, it provides the output in bold.

Client 1 + (Condition 1 OR Condition 2 OR Condition 3) + (Option 1 OR Option 2) = 15

(Client 2 OR Client 3 OR Client 4 OR Client 5) + (Condition 1 OR Condition 2 OR Condition 3) + (Option 1 OR Option 2) = 1

Condition 4 + (Option 1 OR Option 2) = 8

Condition 5 + (Option 2 OR Option 2) = 16

Option 3 = 1000

 

[kweaver]

Let me try to understand what you mean by the above.
Since your illustration appears to need a value for each row (ticket?), maybe you could start by indicating what value you'd expect for ticket1 and then ticket2, etc.

 

[MrNerd]

Let me try to understand what you mean by the above.
Since your illustration appears to need a value for each row (ticket?), maybe you could start by indicating what value you'd expect for ticket1 and then ticket2, etc.

Sure!

Ticket 1 = 15
Ticket 2 = 1000
Ticket 3 = 1
Ticket 4 = 15
Ticket 5 = 8
Ticket 6 = 1000
Ticket 7 = 1000
Ticket 8 = 1000
Ticket 9 = 1000
Ticket 10 = 1000

 

[kweaver]

This criteria doesn't make sense: Condition 5 + (Option 2 OR Option 2) = 16

Lastly, for now, the conditions are tested from your list, top to bottom???

 

[kweaver]

Phew. Maybe this?

Book1
	A	B	C	D	E
1	Ticket	Client	Condition	Option	Score
2	1	Client 1	Condition 1	Option 1	15
3	2	Client 2	Condition 2	Option 3	1000
4	3	Client 2	Condition 3	Option 2	1
5	4	Client 1	Condition 2	Option 2	15
6	5	Client 4	Condition 4	Option 2	8
7	6	Client 3	Condition 4	Option 3	1000
8	7	Client 5	Condition 4	Option 3	1000
9	8	Client 1	Condition 5	Option 3	1000
10	9	Client 1	Condition 3	Option 3	1000
11	10	Client 3	Condition 3	Option 3	1000
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=IF(AND(B2="Client 1",OR(C2="Condition 1",C2="Condition 2",C2="Condition 3"),OR(D2="Option 1",D2="Option 2")),15,IF(AND(B2<>"Client 1",OR(C2="Condition 1",C2="Condition 2",C2="Condition 3"),D2<>"Option 3"),1,IF(AND(C2="Condition 4",OR(D2="Option 1",D2="Option 2")),8,IF(AND(C2="Condition 5",D2="Option 2"),16,1000))))

 

[MrNerd]

This criteria doesn't make sense: Condition 5 + (Option 2 OR Option 2) = 16

Lastly, for now, the conditions are tested from your list, top to bottom???

So Ticket 4 has client 1, condition 2 and option 2 so it would fit the 15 parameters
For 16, it just needs to be condition 3 and one of the two options to be 16
They can be run in any order.

The numbers are just text representing minutes or hours, not a calculation of the factors. So to give you some context, we have certain response times we need to meet based on client contracts. So for client 1, we need to handle their different priority tickets (or conditions) in 15 minutes if that priority is 1,2 or a 3 and if it is in their live domain (option 1) or both live and non live domains (option 2. So if they meet that criteria of being for client and the priority of their ticket is 1,2 or 3 and they are in their live domain, or both domains, we need to get the turnaround time on their ticket is 15 minutes.