# Table Lookup with Two Criteria

#### andybason

##### Board Regular
Hi,

I have a table where column A contains a list of numbers and column B has either the word "Low" or "Ok". I would like a formula to return the word "Reorder" if 2 or more values in column A are in total greater than 30 and those values have the word Low in column B.

Can anyone offer any suggestions?

Thanks

Andy

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### godsaaint

##### Active Member
Adjust B2:B16 and to A2:A16 to the real ranges:

=IF(SUMIF(B2:B16,"Low",A2:A16) > 30,"Reorder","Ok")

EDIT: forgot to take into account "2 or more values":

=IF(AND(COUNTIF(B2:B16,"Low")>1,SUMIF(B2:B16,"Low",A2:A16)>30),"Reorder","Ok")

Last edited:

#### AlanY

##### Well-known Member
try this

Excel 2012
AB
129Low
233Ok
328Ok
425Low
532Ok
626Ok
733Low
826Ok
928Low
1033Low
11
12Recorder
Sheet2
Cell Formulas
RangeFormula
B12=IF(COUNTIFS(A1:A10,">30",B1:B10,"Low")>1,"Recorder","?")

#### andybason

##### Board Regular
Hi guys,

Thanks for your replies.

godsaaint, can your formula be adapted so that it excludes the values in the row where I put the formula? For example, if I put the formula in C5 it should ignore the values in A5 and B5 and only trigger when the criteria is met by other rows?

Thanks

Andy

#### godsaaint

##### Active Member
Hi guys,

Thanks for your replies.

godsaaint, can your formula be adapted so that it excludes the values in the row where I put the formula? For example, if I put the formula in C5 it should ignore the values in A5 and B5 and only trigger when the criteria is met by other rows?

Thanks

Andy

Sure,

try:

=IF(AND(SUMPRODUCT(--(B1:B16="LOW"),--(ROW(B1:B16)<>ROW())) > 1,SUMPRODUCT(--(B1:B16="LOW"),--(ROW(B1:B16)<>ROW()),A1:A16) > 30),"Reorder","Ok")

Replies
7
Views
418
Replies
1
Views
231
Replies
3
Views
118
Replies
2
Views
2K
Replies
4
Views
810

1,195,908
Messages
6,012,237
Members
441,687
Latest member
urimagic

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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