# If statement with multiple AND/OR criteria

#### aleonard

##### New Member
 A B C D 10,000 400 23,000 2,498 50,000 -1325 26,000 57 20,000 505 37,000 -104 30,000 -8,000 21,000 -1,035 17,000 6,032 14,000 2,045

<tbody>
</tbody>

Hi All,

How can I write an If statement that scans all of Column B and Column D looking for any results that are BOTH less than -4,000 AND less than -4% of the corresponding value immediately to its left? (Example: the 4th value in the B column would be the only result that would satisfy both criteria, because -8,000 is less than -4,000 AND -8,000 is less than -4% of 30,000.

Thanks so much for the help - this forum is incredible.

-A

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
You haven't said how you want the results displayed - the easiest method would be via conditional formatting...
Excel Workbook
ABCD
110,00040023,0002498
250,000-132526,00057
320,00050537,000-104
430,000-8,00021,000-1,035
517,0006,03214,0002,045
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B11. / Formula is =AND(B1<-4000,B1<(A1*-4%))Abc
D11. / Formula is =AND(D1<-4000,D1<(C1*-4%))Abc
B21. / Formula is =AND(B1<-4000,B1<(A1*-4%))Abc
D21. / Formula is =AND(D1<-4000,D1<(C1*-4%))Abc
B31. / Formula is =AND(B1<-4000,B1<(A1*-4%))Abc
D31. / Formula is =AND(D1<-4000,D1<(C1*-4%))Abc
B41. / Formula is =AND(B1<-4000,B1<(A1*-4%))Abc
D41. / Formula is =AND(D1<-4000,D1<(C1*-4%))Abc
B51. / Formula is =AND(B1<-4000,B1<(A1*-4%))Abc
D51. / Formula is =AND(D1<-4000,D1<(C1*-4%))Abc

 Row\Col A​ B​ C​ D​ E​ F​ G​ 1​ -4000​ value(s) 2​ 10,000​ 400​ 23,000​ 2,498​ 4%​ -8000​ 3​ 50,000​ -1325​ 26,000​ 57​ count 4​ 20,000​ 505​ 37,000​ -104​ 1​ 5​ 30,000​ -8,000​ 21,000​ -1,035​ 6​ 17,000​ 6,032​ 14,000​ 2,045​

F4, control+shift+enter (cse), not just enter:
Rich (BB code):
``````
=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
IF(B2:D6<F1,IF(B2:D6<F2*A2:C6,1))))
``````

G2, cse and copy down:
Rich (BB code):
``````
=IF(ROWS(\$G\$2:G2)<=\$F\$4,
SMALL(IF(MOD(COLUMN(\$B\$2:\$D\$6)-COLUMN(\$B\$2),2)=0,
IF(\$B\$2:\$D\$6<\$F\$1,IF(\$B\$2:\$D\$6<\$F\$2*\$A\$2:\$C\$6,\$B\$2:\$D\$6))),
ROWS(\$G\$2:G2)),"")
``````

Sorry, I should have clarified. The output should just be a cell that says YES if any cells in Columns B or D match both of these criteria, and a NO if none do.

Thanks so much,
A

 Row\Col A​ B​ C​ D​ E​ F​ G​ 1​ -4000​ value(s) 2​ 10,000​ 400​ 23,000​ 2,498​ 4%​ -8000​ 3​ 50,000​ -1325​ 26,000​ 57​ count 4​ 20,000​ 505​ 37,000​ -104​ 1​ 5​ 30,000​ -8,000​ 21,000​ -1,035​ 6​ 17,000​ 6,032​ 14,000​ 2,045​

<tbody>
</tbody>

F4, control+shift+enter (cse), not just enter:
Rich (BB code):
``````
=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
IF(B2:D6<f1,if(b2:d6<f2*a2:c6,1))))
</f1,if(b2:d6<f2*a2:c6,1))))
``````

G2, cse and copy down:
Rich (BB code):
``````
=IF(ROWS(\$G\$2:G2)<=\$F\$4,
SMALL(IF(MOD(COLUMN(\$B\$2:\$D\$6)-COLUMN(\$B\$2),2)=0,
IF(\$B\$2:\$D\$6<\$F\$1,IF(\$B\$2:\$D\$6<\$F\$2*\$A\$2:\$C\$6,\$B\$2:\$D\$6))),
ROWS(\$G\$2:G2)),"")
``````

Hi Aladin, your first formula I can't seem to get to work: is it missing more than a ) ??
Is the count heading meant to be next to the value heading?
Sorry if I am a bit confused, always love all your work!

Hi Aladin, your first formula I can't seem to get to work: is it missing more than a ) ??
Is the count heading meant to be next to the value heading?
Sorry if I am a bit confused, always love all your work!

Vagaries of html...

F4, control+shift-enter:
Rich (BB code):
``````
=SUM(IF(MOD(COLUMN(B2:D6)-COLUMN(B2),2)=0,
IF(B2:D6 < F1,IF(B2:D6 < F2*A2:C6,1))))
``````

G2, control+shift+enter and copy down:
Rich (BB code):
``````
=IF(ROWS(\$G\$2:G2)<=\$F\$4,
SMALL(IF(MOD(COLUMN(\$B\$2:\$D\$6)-COLUMN(\$B\$2),2)=0,
IF(\$B\$2:\$D\$6 < \$F\$1,IF(\$B\$2:\$D\$6 < \$F\$2*\$A\$2:\$C\$6,\$B\$2:\$D\$6))),
ROWS(\$G\$2:G2)),"")
``````

To see why we need such a formula, change the value of D6 to -9,000.

Thankyou!!

Replies
2
Views
406
Replies
10
Views
549
Replies
2
Views
324
Replies
7
Views
1K
Replies
2
Views
332

1,196,476
Messages
6,015,436
Members
441,895
Latest member
Zululander

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