Max if

Good evening,

I am looking for a way to use the MAX function and IF function together so that the combination replicates the product of the AVERAGEIFS function.

For example:

On my sample worksheet, I have four columns: NFPmove1, NFPsafe, NFPvalid, NFPforecast. Using the average if function, I find the average of the data in 'NFPmove1' in which the corresponding row of the three other columns contain the specified condition.

Formula:

Please Login or Register  to view this content.

How do I write this formula to find the MAX of NFPmove1 rather than the AVERAGE?

Thanks for the help

You have to do it as an array formula, which means committing the formula using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>. Here's the formula:

Formula:

Please Login or Register  to view this content.

Note that all the conditions are in their own brackets and are ANDed together by means of the * symbol - only if all of them are true for any one row will the value of cell A on that row be considered by the MAX function.

Hope this helps.

Pete

Thanks Pete, that was quick!