Maximum Value by Month

Excel 2019+MAXIFSEOMONTH

To find the biggest value in a given month — the largest February order, the peak daily total per month — combine MAXIFS with two date boundaries, the same trick used for summing by month.

Quick formula: for the max of column B in the month starting at E2:

=MAXIFS(B:B, A:A, ">="&E2, A:A, "<="&EOMONTH(E2,0))

The two date conditions fence in exactly one calendar month; MAXIFS returns the largest value inside it.

Functions used (tap for the full reference guide):

MAXIFS EOMONTH

The example

Dated orders. We want the biggest February order.

	A	B	D	E
1	Date	Amount	Month	Max
2	1/05	$120	Feb 1	$200
3	2/03	$200
4	2/21	$150
5	3/10	$300

The formula

The largest February order — March’s $300 is excluded:

=MAXIFS(B2:B5, A2:A5, ">="&E2, A2:A5, "<="&EOMONTH(E2,0)) // max of Feb {200,150} = 200

How it works

Same month-fencing as Sum by Month, but taking the max:

A2:A5, ">="&E2 keeps rows on or after the 1st of the month.
A2:A5, "<="&EOMONTH(E2,0) keeps rows on or before the month-end.
MAXIFS returns the largest B value among the rows that pass both — $200 for February.
Swap MAXIFS for MINIFS to get the smallest value in the month.

Try it: interactive demo

Live demo

Pick a month; see its largest order.

Month

Max in month:

Variations

Smallest in the month

MINIFS, same boundaries:

=MINIFS(B2:B5, A2:A5, ">="&E2, A2:A5, "<="&EOMONTH(E2,0))

Max by month AND category

Add another condition pair:

=MAXIFS(B2:B5, A2:A5, ">="&E2, A2:A5, "<="&EOMONTH(E2,0), C2:C5, "West")

Legacy array version

Pre-2019, Ctrl+Shift+Enter:

=MAX(IF(TEXT(A2:A5,"yyyy-mm")=TEXT(E2,"yyyy-mm"), B2:B5))

Pitfalls & errors

MAXIFS needs Excel 2019+. Older versions show #NAME? — use the array MAX(IF()) version.

Don’t match the month number alone. MONTH()=2 would include February of every year. The first-of-month / EOMONTH pair is year-safe.

Returns 0 for an empty month. If no orders fall in the month, MAXIFS gives 0, not an error.

Practice workbook

📊

Download the free Maximum Value by Month practice workbook

Dated orders with MAXIFS-by-month (result shown), the MINIFS and category variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the maximum value in a specific month in Excel?

Use MAXIFS with two date boundaries: =MAXIFS(B:B, A:A, ">="&first_of_month, A:A, "<="&EOMONTH(first_of_month,0)). This fences in one calendar month and returns its largest value.

How do I get the smallest value by month instead?

Use the same date boundaries with MINIFS: =MINIFS(B:B, A:A, ">="&E2, A:A, "<="&EOMONTH(E2,0)).

How do I do max-by-month in Excel 2016?

MAXIFS isn't available, so use an array formula: =MAX(IF(TEXT(dates,"yyyy-mm")=TEXT(month,"yyyy-mm"), values)) entered with Ctrl+Shift+Enter.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MAXIFS · EOMONTH