There are quite a few questions on Stack Overflow about doing a conditional MIN and MAX in Excel e.g. Excel: Find min/max values in a column among those matched from another column

However, I don't think the following question is covered.

Normally the MIN and MAX functions will ignore blank rows, however it seems that if used in conjunction with a conditional array formula then they will NOT ignore.

For instance

If I enter the array formula `=MAX(IF(A1:A8="A",B1:B8))`

then I get zero, when I really want to see -1, since of all the non-blank 'A' rows, the maximum is -1.

I thought that the following array formula would work `=MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8))`

but it ALWAYS returns zero

# Best How To :

You nearly had it! However, in an array formula, you cannot replicate an "AND" construction so straightforwardly, in essence since the return from the AND function is always a single value, never an array.

Hence, your attempt:

`=MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8))`

would initially correctly resolve to (using the values you posted):

`=MAX(IF(AND({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}),B1:B8))`

though the AND function would then look at that those two arrays of Boolean TRUE/FALSE returns and return a single value, i.e. FALSE (since there is at least one FALSE amongst those 16 entries).

The correct syntax would be:

`=MAX(IF(A1:A8="A",IF(B1:B8<>"",B1:B8)))`

Regards