Table Lookup with Two Criteria

[andybason]

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

 

[godsaaint]

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")

 

[AlanY]

try this

Excel 2012
	A	B
1	29	Low
2	33	Ok
3	28	Ok
4	25	Low
5	32	Ok
6	26	Ok
7	33	Low
8	26	Ok
9	28	Low
10	33	Low
11
12		Recorder
Sheet2

Cell Formulas
Range	Formula
B12	=IF(COUNTIFS(A1:A10,">30",B1:B10,"Low")>1,"Recorder","?")

 

[andybason]

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]

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")