Lesson#135: MAXIF and MINIF with function IF

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

Here I have put names abc, xyz, qst, fgh in A column. Many of them are being repeated 3, 4 or 5 times. Beside them in the column B I have put some values. Now in D column I have put the criteria names. I need to make a formula to derive the Maximum and Minimum values for each criteria in column 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 above picture.

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

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Tagged with: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *