How to Fix MAXIFS #NAME Error in Excel 2016
Question details
The user needs a way to calculate a maximum value based on multiple conditions in Excel 2016, as the MAXIFS function is unsupported and results in an error.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Attempting to extract the maximum value from a dataset that meets specific criteria across different columns in an older version of Excel.
- Observed behavior
- The MAXIFS function returns a #NAME? error because the function was introduced in later Excel versions and does not exist in Excel 2016.
Verify your Excel version by going to File > Account. If you are using Excel 2016 or earlier, ensure your dataset ranges are perfectly aligned and equal in size before applying alternative array formulas.
Use a Nested MAX and IF Array Formula
Replicate the MAXIFS logic by combining the MAX and IF functions in an array formula, which forces Excel to evaluate multiple conditions simultaneously.
Since Excel 2016 does not natively recognize the MAXIFS function, typing it directly will trigger a #NAME? error. However, you can achieve the exact same conditional maximum calculation by nesting IF statements inside a MAX function.
Because this involves evaluating multiple rows at once, it must be executed as an array formula. This means using a specific keyboard shortcut rather than just pressing Enter.
Click on the cell where you want the calculated maximum value to be displayed.
Type the formula combining MAX and IF. For example: =MAX(IF(data!$C$2:$C$338411='All Countries'!$A6, IF(data!$D$2:$D$338411>='All Countries'!$B$2, IF(data!$D$2:$D$338411<'All Countries'!$B$3, data!$AK$2:$AK$338411))))
Do not simply press the Enter key. Instead, hold down Ctrl + Shift + Enter simultaneously. Excel will automatically add curly braces {} around the formula to indicate it is an array formula.
Install a Custom Excel Add-in
Use a third-party custom functions add-in to backport newer formulas like MAXIFS to Excel 2016.
Calculate Conditional Maximums Easily with WPS Office
WPS Spreadsheet natively supports the MAXIFS function out of the box. You don't need to struggle with complex Ctrl+Shift+Enter array formulas or install third-party add-ins to get your work done.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the conditional data.
- 2. Enter the MAXIFS formula: Select the target cell and simply type your standard formula: =MAXIFS(max_range, criteria_range1, criteria1...).
- 3. Press Enter to calculate: Hit Enter. WPS Spreadsheet instantly calculates the result without returning a #NAME? error and without requiring Ctrl+Shift+Enter.
Frequently Asked Questions
Why does my array formula return 0 instead of the maximum value?
This commonly happens if the criteria text does not match the actual data, if the array ranges are not completely identical in size, or if the target values are formatted as text instead of numbers. Ensure all ranges (e.g., $C$2:$C$100 and $D$2:$D$100) are exactly the same length.
Do I need to press Ctrl+Shift+Enter every time I edit the formula?
Yes. In Excel 2016 and older versions, every time you double-click or modify an array formula, you must re-confirm it by pressing Ctrl+Shift+Enter. If you only press Enter, the formula will break or return incorrect results.
Which versions of Excel support the MAXIFS function natively?
The MAXIFS function is natively built into Excel 2019, Excel 2021, and Microsoft 365. If you are using Excel 2016, 2013, or older, it will result in a #NAME? error.
Can I replace MINIFS in Excel 2016 using the same method?
Yes. You can find the minimum value with conditions in older Excel versions by combining the MIN and IF functions in an array formula. Just replace MAX with MIN: =MIN(IF(criteria_range=criteria, value_range)) and confirm with Ctrl+Shift+Enter.
