# MAX with criteria fails when using negative numbers

1. ## MAX with criteria fails when using negative numbers

Hi,

I'm using MAX with SUMPRODUCT to find the maximum value in a range based on a corresponding range of TRUE/FALSE criteria. The problem is when the values are all negatives, the formula result equals zero when it should equal the smallest negative value.

In column A, I have -1, -2 and -3

In column B, I have TRUE, TRUE and FALSE.

The formula is =SUMPRODUCT(MAX((B1:B3=TRUE)*(A1:A3)))

Now, I could use the LARGE function, but why does MAX evaluate to zero when it should evaluate to -1???

Thanks,

Lawrence  Register To Reply

2. ## Re: MAX with criteria fails when using negative numbers

Because you are multiplying FALSE * -3 = 0 so you get zero as the result, use an "array formula" like this:

=MAX(IF(B1:B3=TRUE,A1:A3))

confirmed with CTRL:+SHIFT+ENTER  Register To Reply

3. ## Re: MAX with criteria fails when using negative numbers

Try this, comfirmed with ctrl+shift+enter

Formula:  `Please Login or Register  to view this content.`

For above solution, use Evaluate Function and you'll see the problem  Register To Reply