Fix a MIN IF Formula Returning #VALUE! in Excel 2016
Question details
The user needs a conditional minimum formula that works in Excel 2016 without returning #VALUE!.

- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Calculating conditional minimum, maximum, or standard deviation values from ranges that may be referenced with INDIRECT.
- Observed behavior
- A conditional formula using INDIRECT returns a #VALUE! error.
Identify the cells containing the numbers, the criteria range, and the cell containing the criterion. Check that the value and criteria ranges cover the same number of rows.
Use a conditional MIN array formula in Excel 2016
Use this formula to find the minimum matching value while excluding blank cells.
Excel 2016 does not have a native MIN.IF function. A MIN and IF array formula provides a conditional minimum instead.
Select the result cell and enter =MIN(IF((A2:A100<>"")*(B2:B100=E2),A2:A100)). Replace A2:A100 with the numeric value range, B2:B100 with the criteria range, and E2 with the criterion.
Press Ctrl+Shift+Enter instead of Enter in Excel 2016. Excel adds braces around the formula in the formula bar; do not type the braces yourself.
Compare the result with the matching rows in columns A and B. If you edit the formula later, confirm it again with Ctrl+Shift+Enter.

Check range sizes and INDIRECT references
Use these checks if the array formula still returns an error or relies on INDIRECT.
Calculate a Conditional Minimum in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use a MIN and IF array formula to find the smallest value that meets your criterion.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Enter the formula: Select a result cell and enter =MIN(IF((A2:A100<>"")*(B2:B100=E2),A2:A100)), replacing the ranges and criterion with your own.
- 3. Confirm and check: Confirm the formula as an array formula with Ctrl+Shift+Enter, then compare the result with the matching source rows.
- 4. Save the workbook: Select File > Save As and choose an Excel workbook format if you need to share the file with Excel users.

Frequently Asked Questions
Why can't I use MIN.IF in Excel 2016?
MIN.IF is not a native Excel 2016 function. Use MIN with IF, such as =MIN(IF((A2:A100<>"")*(B2:B100=E2),A2:A100)), and confirm it with Ctrl+Shift+Enter.
Why does my array formula still show #VALUE!?
Check that the value and criteria ranges have the same dimensions and that the source cells do not contain errors. Use Formulas > Evaluate Formula to inspect the calculation.
Does INDIRECT cause #VALUE! errors?
INDIRECT can contribute to formula problems if it points to an unintended range, although an invalid reference commonly returns #REF!. Test the INDIRECT expression in an empty cell and correct its reference text.
Can I use the same approach for a conditional maximum?
Yes. Replace MIN with MAX in the array formula and confirm it with Ctrl+Shift+Enter in Excel 2016. Keep the value and criteria ranges the same size.
Why does a conditional standard deviation formula return an error?
Check the source values, matching range sizes, and array entry. STDEV.S also needs at least two matching numeric values; otherwise, it returns an error.




