Formula Errors

Fix a MIN IF Formula Returning #VALUE! in Excel 2016

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

The user needs a conditional minimum formula that works in Excel 2016 without returning #VALUE!.

Fix a MIN IF Formula Returning #VALUE! in Excel 2016
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

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.

2
Confirm the array formula

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.

3
Check the result

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.

Use a conditional MIN array formula in Excel 2016
Check for matches: MIN can return 0 when no numeric values match the condition. Check the matching rows before treating 0 as a valid minimum.
WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
  2. 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. 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. 4. Save the workbook: Select File > Save As and choose an Excel workbook format if you need to share the file with Excel users.
Calculate conditional minimums while excluding blank cellsOpen and edit Microsoft Excel XLSX and XLS workbooksUse familiar spreadsheet formulas and range referencesWork with a free, easy-to-use Office suite
microsoft office alternative - wps office

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.