Yes. In Excel, use MAXIFS to return the largest value from rows whose date matches a chosen date. With dates in A2:A100, values in B2:B100, and the date to check in D2, enter =MAXIFS($B$2:$B$100,$A$2:$A$100,D2); if the matching values are 18, 10, and 14, the result is 18.
How MAXIFS finds the maximum for a date
MAXIFS calculates a maximum only from cells that meet one or more conditions. Its first argument is the range containing the numbers to compare; the next arguments specify a criteria range and a condition. In the formula above, Excel examines the dates in column A, keeps rows equal to D2, and returns the largest corresponding number in column B.
For example, suppose a small sales log contains these records:
- January 5, 2026 — 10
- January 6, 2026 — 12
- January 5, 2026 — 18
- January 7, 2026 — 9
- January 6, 2026 — 15
- January 5, 2026 — 14
For January 5, the matching values are 10, 18, and 14, so the maximum is 18. The other dates and their values do not affect that result. MAXIFS is available in Microsoft 365 and Excel 2019 or later. In current Microsoft 365 for Windows, type the formula in a cell and press Enter; it does not require Ctrl+Shift+Enter.
Set up a date-by-date summary
A useful layout is to keep the source records in two columns and make a separate summary list of dates. Put transaction dates in A2:A100, amounts or measurements in B2:B100, and summary dates in D2:D20. Enter the formula in E2, then fill it down next to the remaining summary dates. The dollar signs keep the source ranges fixed as the formula is copied, while D2 changes to D3, D4, and so on.
For that layout, if D2 is January 5 and the matching values are 10, 18, and 14, the formula in E2 returns 18. If D3 is January 6 and its values are 12 and 15, the copied formula returns 15. You can build the date list yourself, or create one by copying the source dates to another area and using Data > Remove Duplicates. Sort the resulting dates if you want the summary in chronological order.
Make sure the entries in the summary column are real Excel dates, not text that merely looks like a date. Excel stores dates as serial numbers, so a cell formatted to display January 5, 2026 can still be compared as a date value. Different display formats, such as 5-Jan and 1/5/2026, do not prevent a match if the underlying date values are equal.
When the date cells also contain times
A common reason for an unexpected zero is that the source values include time-of-day information. Excel stores a date and time together as a number: the date is the whole-number portion and the time is a fraction of a day. A source entry for January 5 at 2:00 p.m. is therefore not exactly equal to a summary cell containing January 5 at midnight.
With dates and times in A2:A100, values in B2:B100, and a date-only target in D2, use a start-inclusive, next-day-exclusive test: =MAXIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&D2+1). If January 5 has records at 9:00 a.m. with value 10, 2:00 p.m. with value 18, and 11:30 p.m. with value 14, this returns 18.
The lower boundary includes midnight at the start of the chosen date, while the upper boundary excludes midnight at the start of the following date. That pattern captures every time on the requested date without needing to guess the last possible time value. It also avoids accidentally including the next day’s records.
Use more than one condition
You can restrict the maximum by date and another field, such as region, product, or employee. For example, put dates in A2:A100, regions in B2:B100, and sales in C2:C100. Put a target date in F2 and a region name in G2. With January 5 in F2 and North in G2, if North’s sales on that date are 10 and 18, while South has a sale of 30, the result should be 18.
For this layout, enter =MAXIFS($C$2:$C$100,$A$2:$A$100,$F2,$B$2:$B$100,$G2). Each criteria range must have the same dimensions as the maximum range. Here, all three ranges cover rows 2 through 100, so Excel can test each date and region against the sale on the same row. Add more criteria-range and criteria pairs when you need additional filters.
Return the record associated with the maximum
MAXIFS returns the maximum number, not the name or other details from the row where that number appears. To return a related label, first calculate the maximum in E2. For example, assume dates are in A2:A100, values in B2:B100, item names in C2:C100, D2 contains the requested date, and E2 contains that date’s maximum. If the first matching row with the maximum is named Widget B, this formula returns Widget B: =XLOOKUP(1,($A$2:$A$100=D2)*($B$2:$B$100=E2),$C$2:$C$100,"No match").
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. This formula returns the first row that matches both the date and the maximum. If two records share the same maximum, it does not return both names. To show every tied record in Microsoft 365, use FILTER with the same two tests; it can spill all matching labels into nearby cells. Leave the spill area clear so the results have room to appear.
Handle missing dates and unexpected results
When no rows meet the criteria, MAXIFS returns 0. That can be confusing if zero is a valid measurement, or if the real maximum could be negative. You can test whether the date appears before calculating the maximum. With dates in A2:A100, values in B2:B100, and the requested date in D2, if D2 has no matching source date the following formula displays No matching date; if the date exists with values 10, 18, and 14, it returns 18: =IF(COUNTIF($A$2:$A$100,D2)=0,"No matching date",MAXIFS($B$2:$B$100,$A$2:$A$100,D2)).
If the result still looks wrong, check the data and criteria in this order:
- Confirm that the date cells contain numeric Excel dates rather than text. Changing the number format alone does not convert text into a date.
- Check for hidden times in the source dates. Use the date-range formula in the earlier section if timestamps are present.
- Verify that the maximum range and criteria ranges start and end on the same rows.
- Look for blank or text entries in the values column. MAXIFS evaluates numeric values; text is not a numeric maximum.
- Check the target cell for an unintended time, or for a date that differs by year even though the month and day look similar.
Excel’s date interpretation can also depend on regional settings. An entry such as 03/04/2026 can mean March 4 or April 3 depending on the system’s date convention. When importing data, verify that Excel parsed the dates as intended rather than relying only on how the cells are displayed.
MAXIFS compared with other ways to summarize
For a reusable formula beside each date, MAXIFS is usually the most direct choice. It is easier to audit than a long conditional array formula and can handle several conditions. If you need a report rather than a live formula, a PivotTable is another practical option: choose Insert > PivotTable, place the date field in Rows and the value field in Values, then use Value Field Settings to summarize the values by Max instead of Sum.
PivotTables are useful for exploring large logs, changing groupings, and filtering categories interactively. Their results usually need a refresh after the source data changes, unless refresh behavior is configured. A formula-based summary recalculates as its referenced data changes. If the source contains timestamps, group or normalize them to calendar dates before expecting a PivotTable to combine all times on the same day; otherwise different timestamps may appear as separate row labels.
In older Excel versions without MAXIFS, a PivotTable avoids needing a newer function. Another option is a conditional array formula, but its entry requirements depend on the Excel version and it is easier to mis-edit. For a workbook shared among people using different Excel releases, check which version they use before choosing a formula-based solution.
Frequently Asked Questions
Can MAXIFS find the largest value for each date automatically?
Yes. Put each date you want to summarize in a separate summary row, use that cell as the date criterion, and fill the MAXIFS formula down. Each row then calculates its own maximum.
Does MAXIFS work when several rows have the same date?
Yes. It checks every row that meets the date condition and returns the largest numeric value among those rows. Repeated dates are exactly the kind of case this function can summarize.
Why does MAXIFS return zero even though the date is present?
Check whether the source date includes a time, whether the target is a real date, and whether the date values actually match. MAXIFS also returns zero when no rows meet its criteria, so a no-match check can help distinguish that case from a genuine zero maximum.
Can MAXIFS return the name next to the highest value?
Not by itself: MAXIFS returns a number. Use a lookup based on both the date and the maximum to return a related name. If there are ties, use a method that returns multiple matches if you need every tied record.
Can I use MAXIFS for a date and a category?
Yes. Add another criteria-range and criteria pair, such as a category column and a cell containing the category to match. Keep the maximum range and every criteria range the same size.
Related
- Excel Shortcut for Selecting a Column in One Step
- Excel Shortcut for Filtering a Table: Keys and Tips
- Excel Shortcut for Inserting a Row on Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Page Numbers in Word for APA Style PapersTo add APA-style page numbers in Microsoft Word, choose Insert > Page Number > Top of Page >…
- UK Date Format in Google Sheets: Set Dates CorrectlyTo get UK dates in Google Sheets, set the spreadsheet locale to United Kingdom, then format the date…
- Remove Date Format in Google Sheets Without Changing ValuesTo remove a date display in Google Sheets, select the affected cells and choose Format > Number >…
- Excel Undo Shortcut: Ctrl+Z and How It WorksIn Excel for Microsoft 365 on Windows, press Ctrl+Z to undo your most recent action; on a Mac,…
- Insert a Circle Checkbox in Word: Symbols and OptionsTo insert a circle-style checkbox in Word, use Insert > Symbol to add a suitable mark, or draw…
- Export Excel to Word: Methods and Formatting TipsTo export Excel data to Word, select and copy the cells in Excel, then paste them into Word…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.