Max by month

In this example, the goal is to get the max value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you can use a traditional array formula. The article below explains both approaches. For convenience only, the formulas below use the named ranges amount (C5:C16) and date (B5:B16).

Note: the values in E5:E10 are valid Excel dates, formatted to display the month name only with the number format "mmm". See below for more information.

MAXIFS solution

The MAXIFS function can get the maximum value in a range based on multiple criteria. The generic syntax looks like this:

=MAXIFS(max_range,range1,criteria1,range2,criteria2,...)

Note that each condition is applied in pairs: range1 and criteria1 define the first condition, range2 and criteria2 define the second condition, and so on. In this problem, we need to configure MAXIFS to get the max value by month using two conditions: one for a start date, and one for an end date. We start off with the max_range, which contains the values in amount (C5:C16):

=SUMIFS(amount,

To enter a criteria for the start date, we use the named range date (B5:B16) followed by a greater than or equal to operator (>=)  concatenated to cell E5:

=SUMIFS(amount,date,">="&E5,

This works because cell E5 already contains the first day of the month, formatted to display the month only. To enter criteria for the end date, we use the EDATE function to advance one full month to the first day of the next month:

=EDATE(E5,1) // first of next month

We can then use the less than operator (<) to target the correct dates. The final formula in F5, copied down, is:

=SUMIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))

Roughly translated, the meaning of this formula is "Get the maximum value in C6:C16 when the date in B5:B16 is greater than or equal to the date in E5 and less than the first day of the next month". Notice we need to concatenate the dates to logical operators, as required by the MAXIFS function. As the formula is copied down, it returns the max value for each month listed in column E. The named ranges behave like absolute references and don't change, while the reference to E5 is relative and changes at each new row.

Note: another option would be to use the EOMONTH function to get the last day of the current month, then use "<=" as the second logical operator. However, because EOMONTH returns a date that is technically midnight, there is a danger of excluding dates with times that occur on the last of the month. Using EDATE is therefore simpler and more robust.

With hardcoded dates

To use the MAXIFS function with hardcoded dates, the best approach is to use the DATE function like this:

=MAXIFS(amount,date,">="&DATE(2023,1,1),date,"<"&DATE(2023,2,1))

This formula uses the DATE function to create the first day in January 2023 and the first day in February 2023. This is a safer option than entering a date as text, because it guarantees that Excel will interpret the date correctly.

MAX with TEXT

In older versions of Excel without the MAXIFS function, another way to solve this problem is with the MAX function and the TEXT function in a traditional array formula like this:

=MAX((TEXT(date,"mmyy")=TEXT(E5,"mmyy"))*amount)

Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel.

In this version, we use the TEXT function to convert the dates to text strings in the format "mmyy". Because there are 12 dates in the list, the result is an array with 12 values like this:

{"0123";"0123";"0123";"0223";"0223";"0223";"0223";"0323";"0323";"0323";"0423";"0423"}

Next, the TEXT function is used in the same way to extract the month and year from the date in E5:

TEXT(E5,"mmyy") // returns "0123"

The two results above are then compared to each other. The result is an array of TRUE and FALSE values like this:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The TRUE values in this array indicate dates in B5:B16 that are in the same month and year as the date in E5. This array is then multiplied by the values in amount. The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so the formula is evaluated like this:

=MAX((TEXT(date,"mmyy")=TEXT(E5,"mmyy"))*amount)
=MAX({1;1;1;0;0;0;0;0;0;0;0;0}*amount)
=MAX({1000;500;1275;0;0;0;0;0;0;0;0;0})
=1275

Note that the zeroes effectively cancel out values in other months and the MAX function returns 1275 as the max value in January 2023. As the formula is copied down, the relative reference E5 changes at each new row and the formula returns a maximum amount for each date listed in column E. One nice feature of this formula is that it automatically tests for year and month, while ignoring day, making the logic simple and elegant. It would be nice to use this same logic inside the MAXIFS formula as well, but the MAXIFS function is in a group of eight functions that won't accept an array operation as a range argument.

Display dates as names

To display the dates in E5:E10 as names only, you can apply the custom number format "mmm". Select the dates, then use Control + 1 to bring up the Format Cells Dialog box and apply the date format as shown below:

This allows you to use valid Excel dates in column E (required for the formula) and display them as you like.

Pivot Table solution

A pivot table is another excellent solution when you need to summarize data by year, month, quarter, and so on, because it can do this kind of grouping for you without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables.