Lesson#135: MAXIF and MINIF with function IF

In this post, I will show you to make two new functions. These are functions MAXIF and MINIF. It means this formula will show the Maximum or the Minimum value when the criteria will be matched.

Here I have put the names abc, xyz, qst, fgh in the A column. Many of them are repeated 3, 4, or 5 times. Besides them, in column B I have put some values. Now in the D column, I have put the criteria names. I need to make a formula to derive the Maximum and Minimum values for each criterion in columns E and F.

Here I will put the formula =MAX(IF(A2:A16=D2,B2:B16))

But if I press Enter it will not work properly. To make the formula work you need to press Ctrl+Shift+Enter because this is an array formula.

So it will look like { =MAX(IF(A2:A16=D2,B2:B16)) } in the formula tab.

See the results in the above picture.

In the same way, I will make the MINIF function by putting the formula
{ =MIN(IF(A2:A16=D2,B2:B16)) }