In Excel for Microsoft 365, use MAXIFS to return the latest date that matches several conditions. With dates in A2:A100, product names in B2:B100, regions in C2:C100, and the requested product and region in E2 and F2, enter =MAXIFS($A$2:$A$100,$B$2:$B$100,$E$2,$C$2:$C$100,$F$2). Format the result cell as a date if it displays as a number.
How MAXIFS finds the latest matching date
MAXIFS returns the largest numeric value in a range after checking one or more criteria. Excel stores real dates as serial numbers, so the largest date serial represents the latest date. The function tests each row against every condition you supply, then finds the maximum date among rows that pass all of them.
The formula’s first argument is the range containing the values to maximize. After that, each criteria range is paired with a criterion. In the example, Excel checks the product in column B and the region in column C, then returns the maximum date from column A only where both checks match. These conditions are combined with AND logic: a row must meet every supplied criterion.
Each criteria range must have the same dimensions as the max range. If the date range is A2:A100, use B2:B100 and C2:C100—not entire columns or ranges starting on a different row. Keeping the ranges aligned avoids errors and makes it easier to inspect which records the formula evaluates.
Set up the worksheet and enter the formula
A simple data layout makes the calculation easier to verify. Put one record on each row, use a header row, and place the criteria values in separate cells. For example:
- Column A: Order date
- Column B: Product
- Column C: Region
- Cell E2: product to find, such as Widget
- Cell F2: region to find, such as West
- Cell G2: formula result
With dates in A2:A100, products in B2:B100, regions in C2:C100, the chosen product in E2, and the chosen region in F2, enter this in G2:
=MAXIFS($A$2:$A$100,$B$2:$B$100,$E$2,$C$2:$C$100,$F$2)
Suppose the rows matching Widget and West have dates March 4, March 19, and April 2, 2025. The result is April 2, 2025. The dollar signs keep the ranges fixed if you copy the formula elsewhere; the criteria references remain relative, so E2 and F2 can change when copied. In Microsoft 365, MAXIFS is available on Windows and Mac, and in Excel for the web. The ribbon is not needed to enter it: select the result cell, type the formula in the formula bar or cell, and press Enter.
Add more criteria or use different criteria types
To add another condition, append another criteria-range and criteria pair. For example, you might require a department in column D as well as a product and region. Each extra pair narrows the eligible rows; it does not create an alternative condition. The date range and all criteria ranges must still cover corresponding rows.
Criteria can refer to cells, as in the main example, or be entered directly into a formula. Cell references are usually more convenient because users can change the selection without editing the formula. You can also use comparison operators for numeric or date conditions. To find the latest date after a cutoff date stored in H2, include the date range as another criteria range and use a criterion built from the operator and cell value:
With dates in A2:A100, products in B2:B100, the chosen product in E2, and the cutoff date in H2, use:
=MAXIFS($A$2:$A$100,$B$2:$B$100,$E$2,$A$2:$A$100,">"&$H$2)
This returns the latest date for that product that is later than H2. A criterion can use operators such as greater than, less than, or not equal to. For text, a criterion may use a partial match with wildcards, such as an asterisk for any number of characters; exact matching is generally clearer when the data uses consistent labels.
Handle no matches and blank dates
If no rows satisfy the criteria, MAXIFS returns 0. Because zero is not a normal modern date, a result cell formatted as a date may show a confusing date near the start of Excel’s date system. A blank-looking or error-looking result can therefore be a no-match issue rather than a broken formula.
To show a blank when there is no matching positive date, check the count of matching rows with dates greater than zero before calculating the maximum. With dates in A2:A100, products in B2:B100, regions in C2:C100, and requested values in E2 and F2, use:
=IF(COUNTIFS($B$2:$B$100,$E$2,$C$2:$C$100,$F$2,$A$2:$A$100,">0")=0,"",MAXIFS($A$2:$A$100,$B$2:$B$100,$E$2,$C$2:$C$100,$F$2,$A$2:$A$100,">0"))
The date condition excludes blank cells and zero values. The formula returns an empty string if there is no qualifying date; otherwise it returns the latest one. Format the result as a date. If you prefer an explanatory message, replace the empty string with text such as No matching date. Note that a formula returning text will not behave as a date in later date calculations.
Check that Excel recognizes the values as dates
Excel can calculate a maximum only when the dates are numeric date values. A cell may look like a date but contain text—for example, imported data with inconsistent separators or leading apostrophes. Text dates are not treated as ordinary date serial numbers by MAXIFS, so the returned result may be too early or zero even though the sheet appears to contain later dates.
To investigate, select a date cell and change its number format to General. A genuine date displays as a number; text generally remains visibly text. You can also check alignment or use sorting as clues, but formatting alone does not convert text into dates. Convert imported values using a suitable method for their format, such as Text to Columns or a helper formula, then verify the converted dates before relying on the maximum.
Also check whether the source data contains time values. Excel stores a date and time together as a number, with the time represented as a fraction of a day. If two matching records have the same calendar date but different times, MAXIFS returns the later timestamp. A date-only number format hides the time, so the displayed dates may look tied even though the underlying values differ.
Common formula problems to check
- Different range sizes: Make the max range and every criteria range start and end on the same rows.
- Unexpected spaces: A product name with a trailing space will not exactly match the same name without one. Clean imported text or use consistent source values.
- Wrong condition logic: Multiple criteria pairs mean AND. They do not mean “this region or that region.”
- Text instead of date values: Convert the source dates to numeric Excel dates before calculating.
- Unintended blanks or zeros: Exclude non-date values with an additional date criterion when appropriate.
- Display format: A correct serial result can look like a large number. Apply a date format from Home > Number Format.
When debugging, test the criteria one at a time. For instance, temporarily remove the region pair to see whether any dates match the product. Then add it back. This helps distinguish a formula-structure problem from inconsistent labels or missing data.
Use MAXIFS in older Excel versions
MAXIFS is included in Microsoft 365 and Excel 2019 or later, but it is not available in some older releases, including Excel 2016. If you must support an older version, an array formula can evaluate multiple conditions and take the maximum. With dates in A2:A100, products in B2:B100, regions in C2:C100, and criteria in E2 and F2, use:
=MAX(IF(($B$2:$B$100=$E$2)*($C$2:$C$100=$F$2),$A$2:$A$100))
In Microsoft 365, press Enter; dynamic array calculation handles the expression. In older Excel versions that require a traditional array formula, confirm it with Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac, rather than Enter alone. Excel may display braces around the formula after it is entered; do not type those braces yourself. Like the basic MAXIFS formula, this returns 0 if no matching date is found, and text dates still need to be converted.
If the workbook will be opened by people using different Excel versions, MAXIFS may be the more readable option but can produce a function-name compatibility issue in older releases. Confirm the recipients’ versions or use a compatible formula when sharing a workbook with a legacy environment.
Return information from the row with the latest date
Sometimes the goal is not just to show the maximum date, but also to return an associated value, such as the order number or employee on that record. First calculate the maximum date in G2 using the main formula. Then use a lookup that checks the date and the other criteria together. With dates in A2:A100, products in B2:B100, regions in C2:C100, order IDs in D2:D100, the criteria in E2 and F2, and the maximum date in G2, use:
=XLOOKUP(1,($A$2:$A$100=$G$2)*($B$2:$B$100=$E$2)*($C$2:$C$100=$F$2),$D$2:$D$100,"No match")
This returns the first order ID whose date, product, and region all match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If several matching records share the maximum date, this version returns the first one in worksheet order. To return all tied records, use a separate filtering approach, such as FILTER in Microsoft 365 or Excel 2021 and later, rather than assuming the maximum date identifies a unique row.
Frequently Asked Questions
Can MAXIFS use more than two criteria?
Yes. Add a criteria-range and criteria pair for each additional condition. For example, you can require a product, region, and department simultaneously. Every pair uses AND logic, and all ranges must align with the date range.
Does MAXIFS work with dates and times?
Yes, provided the cells contain numeric Excel date-time values. It compares the full stored values, including times, even if the cells are formatted to display only the date.
Why does my result show 0 or a strange date?
Usually no row matches, or the matching date cells are blank or stored as text. Check the criteria values and confirm that the source dates are real Excel dates. Add a no-match check if you want a blank instead of zero.
How do I match either of two regions?
Separate criteria pairs in MAXIFS require both conditions, so listing two regions as separate pairs will not mean OR. One option is to take the larger result from two MAXIFS calculations, one for each region, provided the dates are valid positive values and a no-match zero is handled if needed.
Can I use a date typed directly in the formula?
It is safer to refer to a cell containing a real date. If you construct a comparison criterion, join the operator to the date cell, as in the cutoff example above; this avoids ambiguity from regional date formats and text representations.
Related
- Excel: How to Work Out the Difference Between Two Numbers
- Excel Work Schedule Template: Build and Customize One
- Excel Work Timesheet Template: Build One That Adds Work Hours
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Do Excel Cells Automatically Resize?No. In Microsoft Excel for Microsoft 365 on Windows, columns do not automatically widen and rows do not…
- How to Make Columns in a Word Document, Step by StepTo make columns in a Word document, place your cursor in the text or section you want to…
- How to Change the Slide Ratio in PowerPointTo change a PowerPoint slide ratio in Microsoft 365 for Windows, open Design > Slide Size, choose Widescreen…
- How to Add an Attachment to a Word Mail MergeTo add a different attachment to each Word mail-merge email, put each recipient’s file path in your data…
- How to Copy a Table in Excel Without Losing FormattingTo copy a table in Excel for Microsoft 365 on Windows, select the whole table, press Ctrl+C, click…
- Excel Scatter Plot: How to Remove Values at the OriginTo remove only the point where both coordinates are zero from an Excel scatter plot, make helper columns…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.