Function Problems

How to Use MAXIFS Logic with Replacement Values in Excel

Khadija KhanKhadija Khan Nov 10, 2026 868 views

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.

How to Use MAXIFS Logic with Replacement Values in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Define the virtual array with LET

Start by defining the modified array using the LET function. Select your target cell and type: `=LET(modifiedArr, IF(lookupArr < 0.1, replacementArr, lookupArr), ...)`.

2
Apply criteria using IF

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)`.

3
Wrap with the MAX function

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 LET and MAX(IF) for Dynamic Array Replacement
Array Formula Requirement: In older versions of Excel that do not support dynamic arrays natively, you may need to press Ctrl+Shift+Enter to execute this as an array formula, though modern versions handle it automatically.

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. 1. Open your workbook: Launch WPS Spreadsheet and open your .xlsx dataset.
  2. 2. Enter the LET formula: Select an empty cell and input your `=LET(...)` array formula to process the replacement and MAX logic.
  3. 3. Calculate the result: Press Enter to calculate. WPS automatically handles dynamic arrays, providing the correct maximum value instantly.
Fully compatible with Microsoft Excel (.xlsx) formulas and data structures.Supports modern dynamic array functions like LET to simplify complex calculations.Free and lightweight alternative for powerful data analysis.
microsoft office alternative - wps office

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")`.