# IF FUNCTION W/4 CRITERIA

#### chrisflan

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try to post a 5 row sample along with expected results in lieu of an "abstract" description.

Chris

Why not use COUNTIF?

Something like this perhaps.

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

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.

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

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
HIJK
1034101
11331082
125566444
131022133
14121383
Sheet1

Replies
1
Views
175
Replies
2
Views
66
Replies
16
Views
475
Replies
2
Views
165
Replies
1
Views
119

1,203,051
Messages
6,053,221
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back