The MAXIFS Function in Excel is a Statistical function that returns the maximum value based on one or more than one conditions from the specified cells. The MAXIFS function was introduced in MS Excel 2019.
The formula will return the amount with the expense of Printing & stationary.
Let’s take another example. We wish to find out the student who scored Distinction in any subject and the marks scored are maximum. We use the data below:
The formula to use will be =MAXIFS(B6:B16,C6:C16,”A”,E6:E16,”=Distinction”)
In criteria_range1, C10, C11, and C16 match the criteria of “A” of the corresponding cells in criteria_range2, E10, E11 and E16 match the criteria of Distinction. Finally, of the corresponding cells in max_range, B11 gives the maximum value. The result is therefore 50.
Let’s take this example wherein we would use MAXIFS with an array formula. We wish to use the “MAXIFS” formula that will return the latest date for open tasks for each project in Column E for the data below:
So, for the data example above, we would expect to see 01/03/2017 in Column F on all rows for Project A; 01/05/2017 for all rows of Project B; and 01/01/2017 for all rows of Project C.
The formula to be used is below:
For parentheses, we need to press Ctrl+Shift+Enter the first time we input it because it only works as an array formula. After that, we can drag it over the range where we need it.
Things to remember about the MAXIFS function
#VALUE! error – This is returned when the size and shape of the max_range and criteria_rangeN arguments aren’t the same.
If we are using earlier versions of Excel, we can use an array formula based on MAX and IF to find maximum values with criteria.
#NAME? error – Occurs when we are using an older function of Excel.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.