How to Use MAXIFS Logic with Replacement Values in Excel
Question details
The user wants to replace lookup values that fall below a certain threshold and return the maximum qualifying result using a single-cell formula.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing datasets where specific values below a threshold must be substituted before calculating the maximum value based on multiple criteria.
- Observed behavior
- Needs a dynamic formula to replace values and calculate the conditional maximum without using additional helper columns.
Ensure your spreadsheet software supports Dynamic Arrays and the LET function (available in Office 365, Excel 2021, and recent versions of WPS Office). Identify your lookup array, replacement array, and criteria ranges before building the formula.
Use LET and MAX(IF) for Dynamic Array Replacement
This is the most efficient method to replace values below a threshold and apply multiple criteria within a single cell, avoiding helper columns.
The standard MAXIFS function cannot process arrays modified on the fly because it strictly requires range references. By using the LET function, you can first create a virtual array with the replaced values and then evaluate your conditions against it using a combination of MAX and IF.
Start by defining the modified array using the LET function. Select your target cell and type: `=LET(modifiedArr, IF(lookupArr < 0.1, replacementArr, lookupArr), ...)`.
Nest an IF function to apply your conditions. Multiply the criteria arrays to simulate AND logic within the array formula: `IF((criterion1=criterion1Value)*(criterion2=criterion2Value), modifiedArr)`.
Wrap the IF statement in a MAX function to find the highest value that meets the criteria. The final formula will look like: `=LET(modifiedArr, IF(lookupArr < 0.1, replacementArr, lookupArr), MAX(IF((criterion1=criterion1Value)*(criterion2=criterion2Value), modifiedArr)))`. Press Enter to execute.

Use Python (Pandas and NumPy) for Large Datasets
If you are processing massive datasets outside of standard spreadsheet limits or using Python in Excel, you can use NumPy to filter and find the maximum.
Solve Complex Array Formulas in WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced array formulas, including the LET, MAX, and IF functions. You can easily process complex logic and data replacements just like in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open your .xlsx dataset.
- 2. Enter the LET formula: Select an empty cell and input your `=LET(...)` array formula to process the replacement and MAX logic.
- 3. Calculate the result: Press Enter to calculate. WPS automatically handles dynamic arrays, providing the correct maximum value instantly.

Frequently Asked Questions
Why doesn't the standard MAXIFS function work with modified arrays?
The MAXIFS function is designed to only accept physical ranges as its criteria and max range arguments. It cannot process virtual arrays or arrays generated on the fly by functions like IF or LET.
Can I use multiple thresholds for replacement in the LET function?
Yes. You can nest multiple IF statements or use the IFS function within the variable definition of the LET function to handle multiple replacement thresholds before passing the array to the MAX function.
Will this formula work in older spreadsheet software?
The LET function is available in Office 365, Excel 2021, and recent versions of WPS Office. For older versions, you must omit LET, duplicate the IF array statement inside the formula, and press Ctrl+Shift+Enter.
How do I handle errors if no values meet the criteria?
You can wrap your entire LET formula in an IFERROR function to return a custom message or a zero when the criteria evaluate to an error. For example: `=IFERROR(LET(...), "No Match")`.




