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.
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:
would initially correctly resolve to (using the values you posted):
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: