Function Problems

How to Find the Maximum Date for Multiple Criteria in Excel

Elise Williams Oct 9, 2026 871 views

Question details

The user needs to extract the most recent date for a unique combination of three different attribute columns without manually defining specific ranges for each combination.

Find the Maximum Date for Each Attribute Combination in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting the latest date in a dataset based on multiple dynamic conditions (e.g., three attribute columns) using a single formula.
Observed behavior: The goal is to return the maximum date dynamically using a function that evaluates all given criteria ranges and values.

Before you start

Ensure your date column is formatted as actual Dates and not text, and verify that your spreadsheet software version supports the MAXIFS function.

Solution 1Recommended

Use the MAXIFS Function to Find the Latest Date

MAXIFS is the most efficient and straightforward way to find the maximum value based on one or multiple criteria without manual sorting.

The MAXIFS function evaluates a range of cells, returning the maximum value among those that meet all specified criteria. Because Excel stores dates as serial numbers, the maximum value is the latest date.

Identify your ranges

Determine the ranges for your dates and your attributes. Ensure the date range and all criteria ranges are the exact same size (e.g., A2:A100, B2:B100).

Select the destination cell

Click on the cell where you want the latest date for your specific attribute combination to appear.

Enter the MAXIFS formula

Type the formula using this syntax: =MAXIFS(DateRange, Attribute1Range, Attribute1Value, Attribute2Range, Attribute2Value, Attribute3Range, Attribute3Value).

Apply and format

Press Enter. If the result appears as a 5-digit number, right-click the cell, choose 'Format Cells', and apply a 'Date' format.

Use the MAXIFS Function to Find the Latest Date

Dynamic Updates: This formula will automatically update and return the new latest date if new rows matching your criteria are added to the dataset.

Solution 2

Use a MAX and IF Array Formula (For Older Versions)

If you are using an older version of Excel that does not support the MAXIFS function, you can use an array formula combining MAX and IF.

Quickly Analyze Complex Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like MAXIFS, making it incredibly easy to find the maximum date across complex criteria without hassle. Enjoy a seamless, highly compatible data analysis experience.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your dates and attribute columns.
2. Select the output cell: Click on the cell where you want the maximum date calculation to appear.
3. Enter the MAXIFS formula: Type =MAXIFS( and follow the intuitive syntax tooltip to select your date range followed by your criteria ranges and values.
4. Get your result instantly: Press Enter to calculate the latest date based on your unique attribute combinations, formatting the cell as a Date if necessary.

Fully compatible with Microsoft Excel formulas including MAXIFSFree and lightweight spreadsheet solutionBuilt-in data formatting tools for easy date managementUser-friendly interface for building complex multi-criteria formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MAXIFS formula return 0 or an error?

This usually happens if the date column is formatted as text instead of dates, or if the criteria ranges do not match the exact size of the date range. Ensure all ranges in the formula are exactly the same length.

Why is the result showing as a random number like 44210?

In Excel and WPS Spreadsheet, dates are stored as serial numbers. The calculation worked perfectly, but you need to right-click the cell, select 'Format Cells', and change the number format from 'General' to 'Date'.

Can I use MAXIFS with more or fewer than three attributes?

Yes, the MAXIFS function can handle up to 126 pairs of range/criteria. You can easily adjust the formula to check for just one attribute, or scale it up to check multiple conditions.

Does MAXIFS work with partial text matches?

Yes, you can use wildcards like asterisks (*) for multiple characters or question marks (?) for single characters in your criteria values if you are looking for partial text matches in your attribute columns.

Excel & Data Problems