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)) }**

## Leave a Reply