How to Find the Maximum Value in an Excel Array by Criteria
Question details
The user needs to filter a range of data based on a specific condition and return the highest value within that filtered array.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Performing advanced data analysis where the maximum value of a 2D array or range must be calculated conditionally.
- Observed behavior
- The user is seeking the correct formula syntax to apply criteria to a dataset and calculate the maximum value from the matching subset, requiring combinations of MAX, FILTER, or array operations.
Identify your target data range and the criteria column. Ensure your spreadsheet software is up to date, as modern functions like FILTER require recent versions to work correctly.
Use the MAX and FILTER Functions (Modern Approach)
This is the easiest and most readable method, suitable for modern spreadsheet versions that support dynamic arrays.
The FILTER function isolates the rows that match your criteria, creating a temporary array in the background. The MAX function then evaluates this temporary array to find the highest number.
Click on the empty cell where you want the maximum value to be displayed.
Type the formula using the syntax =MAX(FILTER(data_range, criteria_range=condition)). For example, enter =MAX(FILTER($D$3:$I$29, $C$3:$C$29=$N9)).
Press Enter. The formula will automatically spill or compute the maximum value for the filtered data without needing special keystrokes.
Use Array Multiplication with MAX (Legacy Approach)
Use this approach if you are working with older spreadsheet software that does not support the FILTER function.
Use BYCOL and FILTER for Column Totals
This method is perfect when you need to calculate the maximum sum of columns after filtering the rows by specific criteria.
Find Max Values with Ease in WPS Spreadsheet
WPS Office features a powerful Spreadsheet application that fully supports advanced dynamic array functions like FILTER, BYCOL, and MAX. It provides a seamless, free experience for handling complex data analysis and conditional calculations.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Select the formula cell: Click on the cell where you want the conditional maximum value to appear.
- 3. Input the formula: Enter your formula, such as =MAX(FILTER(A2:C10, A2:A10="Target")).
- 4. Get instant results: Press Enter to calculate the max value instantly using WPS Spreadsheet's robust calculation engine.
Frequently Asked Questions
Does the MAXIFS function work for a 2D array?
No, the standard MAXIFS function requires the max_range and criteria_range to be identically sized, and it typically operates on 1-dimensional arrays (single rows or columns). For a 2D data array, you must use the MAX(FILTER(...)) or array multiplication techniques.
What happens if my criteria match returns no results using MAX and FILTER?
If the FILTER function finds no matching records, it returns an error. You can prevent this by wrapping your formula in an IFERROR function, like =IFERROR(MAX(FILTER(data, criteria)), "No Match"), to display a friendly message instead.
Do I need to use Ctrl+Shift+Enter for these max value formulas?
If you are using modern dynamic array functions like FILTER, or if you are using Microsoft 365 or the latest versions of WPS Office, you only need to press Enter. However, for legacy array multiplication methods on older software versions, Ctrl+Shift+Enter is required.
