Performing MAXIF or MINIF functionality is something that the non-Office 365 copies of Excel lack. If you have Office 365, you can use a MINIFS or MAXIFS function. If you have the Excel as part of office 2016 for example, you won’t be able to do it. In this post, we will be covering how to perform MINIF or MAXIF on a non-Office 356 copy of Excel.
For people who don’t have Office 365, there are three ways to do it:
- Use pivot tables: Using pivot tables and their filters, you can achieve you MINIF or MAXIF objective, by using filters and changing the aggregation function on the values section of the pivot table to be MIN or MAX.
- Use an Array formula: You can use an array formula containing an IF function to check for the fields on your data table and then select the range of values to be the result if the condition is true.
Something similar to MAX(IF(Cells containing criteria = My Criterion) , Range of cells containing numbers to get the max from – contains numbers) (Don’t forget to press CTRL + Shift + Enter as it’s an Array Formula!)
The blue part will yield an Array of TRUE and FALSE values, and the IF function will of course just select the TRUE values from the green part.
And then the MAX function will get the maximum value out of the green part.
I explain that in more detail on the Youtube Video, and I explain as well how to apply AND/OR conditions. So please make sure to watch the video.
3- Use the DMAX/DMIN function: These functions belong to the family of database functions in Excel (Read more about them here)
The only drawback to these functions, is that you have to create headers on your criteria table, and the headers mmust match those on the data table.
Below are the data and criteria table, so that you would gain a better understanding. As you can see, both of them have the same headers, and that is mandatory for the database function to work correctly. You can read more about Database functions here
I hope you like that Tutorial. Please watch the video and let me know if you have any comments or feedback in the comments section below!