Need Formula

[ruthm8611]

I need help constructing a formula to return a result of 2 when 2 out of 3 criteria equal 100%.

 

[West Man]

Something along the lines of =IF(OR(A1+B1=100,A1+C1=100,B1+C1=100),2,"something else")

 

[ruthm8611]

Thank you for your response. Maybe more info will help. I have the formula
IF(OR(goal1>=100,goal2>=100,goal3>=100),2,"Error")))). It returns a 2 when at least one of the goals are =>100. I need it to return a 2 only when two of the goals =>100. Thank you in advance for any help.

goal1 125
goal2 90
goal3 90

 

[West Man]

Ok then try: =IF(COUNTIF(A1:C1,">"&99)=2,2,"ERROR") if it must be exactly 2 or =IF(COUNTIF(A1:C1,">"&99)>=2,2,"ERROR") if it must be at least 2.

This assumes that Goal1, Goal2 and Goal3 are in contigous cells.

 

[Scott Huish]

=if(countif(a1:c1,">=100")=2,2,"error")

 

[ruthm8611]

Thank you very much for your time. Here is the complete formula I've constructed. Every part works except the last (countif) part. I can't get it to return the correct amount.

Here is criteria being used. Thank you in advance for any assistance.

If goals 1 and 2 are >=125% and goal 3 >=100%, 5
If goal 1 >=125% and goals 2 and 3 >=100%, 4
If goals 1,2, and 3 >=100%, 3
If 2 goals >=100%, 2
If 1 goal >=100%,1


=IF(AND(E15>=G14,E16>=G14,E17>=F14),5,IF(AND(E15>=G14,E16>=F14,E17>=F14),4,IF(AND(E15>=F14,E16>=F14,E17>=F14),3,COUNTIF(E15:E17,">=F14"))))

 

[Scott Huish]

I think your formula could be greatly simplified to this:

=COUNTIF(E15:F15,">="&G14)+COUNTIF(E15:G15,">="&F14)

Format as General.

 

[ruthm8611]

Oh my gosh. Thank you so much. I've never used the & in a formula. What does this do in a formula.

 

[Scott Huish]

It is a concatenation operator.

So in the first countif, it will append whatever is in G14 to the >= before it.