SUMPRODUCT and MAX doesnt find me a negative value

[Sarangsood]

i want to find the highest number in a range which , but it should be less than 10.
i am using the formula : =SUMPRODUCT(MAX((B1:B18<10)*B1:B18))

But the problem is the highest value in the range below 10 is -4 , which this formula is unable to find. It returns the value as 0.

thanks

[NBVC]

Try:

=MAX(IF(B1:B18<10,B1:B18))

confirmed with CTRL+SHIFT+ENTER

[tom1977]

my proposition without sum.product

=MAX(IF(B1:B18<10,B1:B18,0))

array formula Ctrl + Shift + Enter

[Sarangsood]

thanks a lot sir. it worked