IF FUNCTION W/4 CRITERIA

[chrisflan]

I have 3 columns of various numbers that need to be reviewed with specific critera. 1st, all columns <=10 = 1, 2nd, 2 columns <=10 = 2 3rd, 1 column <=10 = 3, all others = 4. I have tried SEVERAL formulas. The below formula
will plug the 1,3, and 4, it leaves out the 2. What am I doing wrong?

if(and(h10<=10,i10<=10,j10<=10),1,if(or(same criteria),3,if(or(same criteria),2,4).

(I did not include all the parenthies needed and if you switch the 3 and 2 it leaves out the 3)

Thanks
Chris

 

[Aladin Akyurek]

Try to post a 5 row sample along with expected results in lieu of an "abstract" description.

 

[Norie]

Chris

Why not use COUNTIF?

Something like this perhaps.

=5-COUNTIF(H10:K10, "<="&10)

 

[Oaktree]

If or(same criteria) is true, your formula would return a 3.

It would not be possible for or(same criteria) to be false (to get to the comparison between 2/4) and true (to return a 2) at the same time.

Maybe this would work:

=vlookup(countif(H10:J10,"<=10"),{0,4;1,3;2,2;3,1},2,0)

edit. Or just use Norie's =5-COUNTIF(H10:K10, "<="&10) to take the easy route.

 

[chrisflan]

Example:

h10 i10 j10 k10
3 4 10 1 (all meet the criteria for 1)
33 10 8 2 ( 2 columns meet the criteria for 2)
55 66 44 4 ( none of the columns meet the criteria)
10 22 13 2 ( 2 columns meet the criteria for 2)
12 13 8 3 ( 1 column meets the criteria for 3)

column k10 displays the matched criteria

hope this is less confusing

Chris

 

[Oaktree]

Save for row 13, where I think only 1 column meets the criterion, I think Norie's idea produces those results.

K10 =4-COUNTIF(H10:J10,"<=10") copied down

Book3
	H	I	J	K
10	3	4	10	1
11	33	10	8	2
12	55	66	44	4
13	10	22	13	3
14	12	13	8	3
Sheet1