# 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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1