How to Fix an Excel MINIFS Formula That Is Not Accepted
Question details
The user is unable to execute a MINIFS formula in their spreadsheet because the application is rejecting the formula syntax or returning an error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering a MINIFS formula that may involve R1C1 reference notation or specific regional separator formats.
- Observed behavior
- The formula is not accepted or recognized by Excel due to incompatible reference styles, outdated application versions, or incorrect delimiter usage.
Verify your current version of Excel, as the MINIFS function is only available in Excel 2019, Microsoft 365, and newer releases. Older versions will reject the formula entirely.
Enable R1C1 Reference Style or Convert Formula to A1
Use this solution if your formula is written using R1C1 notation (using numbers for both rows and columns) but your workbook is currently set to the default A1 notation.
Formulas using R1C1 notation will be rejected as invalid text if the workbook expects A1 notation. You can either enable the R1C1 style in Excel settings or translate your formula into standard A1 format.
Click on 'File' in the top-left corner of the ribbon, then select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on the 'Formulas' tab on the left sidebar.
Look under the 'Working with formulas' section and check the box for 'R1C1 reference style'. Click 'OK' to save your changes.
If you prefer to keep A1 style, manually rewrite the formula. For example, use =IFERROR(MINIFS($U$3:$U$93,$U$3:$U$93,">="&B1),"") and ensure the cell references match your layout.
Update Formula Delimiters for Regional Settings
Apply this fix if your operating system uses a comma as a decimal separator, which requires formulas to use semicolons instead of commas.
Use an Array Formula for Older Excel Versions
Use this workaround if you are running Excel 2016 or older, which do not natively support the MINIFS function.
Calculate Conditional Minimums Easily with WPS Spreadsheet
WPS Spreadsheet natively supports modern functions like MINIFS out of the box. It automatically handles A1/R1C1 conversions and regional delimiter formats, providing a smooth, error-free data analysis experience.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook.
- 2. Select Output Cell: Click on the specific cell where you want the minimum value to be displayed.
- 3. Input the Formula: Type =MINIFS( and select your minimum range followed by your criteria range.
- 4. Get Instant Results: Press Enter, and WPS Spreadsheet will automatically parse and calculate the formula.
Frequently Asked Questions
Why does my MINIFS formula return a #NAME? error?
A #NAME? error usually indicates that your version of Excel does not recognize the function. MINIFS was introduced in Excel 2019. If you are using Excel 2016 or earlier, the function is unsupported, and you should use a MIN(IF()) array formula or switch to a modern alternative like WPS Office.
What is the difference between A1 and R1C1 reference styles?
The A1 style identifies columns by letters and rows by numbers (e.g., A1 represents the first column, first row). The R1C1 style uses numbers for both rows and columns (e.g., R1C1 means Row 1, Column 1). Formulas must align with the active reference style in your settings.
Can I use MINIFS across multiple sheets?
Yes, MINIFS works across multiple sheets. You just need to ensure that the sheet name is properly referenced in the formula before the cell ranges, such as =MINIFS(Sheet2!A1:A10, Sheet2!B1:B10, ">5"). Ensure the ranges are identical in size.
