formula help to display instances of <20

[junkforhr]

Hi guys,

I have a data set of which is pretty simple.

Cell A2 will have a numerical value of say 1159, Cell B2 with have a value of 100, C2 will have 589, D2 will have <20 Cell E may have <20. So in a range of cells there will be a numerical value or <20. <20 is a text value.

There will be instances where the value of <20 will be only present once in a range of cells and this is where I need the forumla. Where <20 is only present once in a range, I would like the value of please check to be returned in a helper column.

The data I am checking can have up 10,000 rows and checking them manually is somewhat time consuming.

I hope I have been clear. I am at work and cannot use any adds-on to post even a text example of my data.

 

[taigovinda]

I think this is what you're looking for... copy the formula down:

Excel Workbook
	A	B	C	D	E	F	G
1
2	1159	100	589	<20	<20	-	OK
3	1159	100	589	<20	15	-	PleaseCheck
Sheet1

Cell Formulas
Range	Formula
G2	=IF(SUMPRODUCT(--(A2:E2="<20"))<2,"Please Check","OK")
G3	=IF(SUMPRODUCT(--(A3:E3="<20"))<2,"Please Check","OK")

Hope that helps.
Tai

 

[junkforhr]

Thanks you very much, this worked a treat!

 

[Haseeb Avarakkan]

Also,

=IF(COUNTIF(A2:E2,"*<20")<2,"Please Check","OK")

 

[taigovinda]

Thanks for that one Haseeb, I couldn't figure out how to get the countif to work myself... always nice to see a better solution!

Tai