Explanation
In this example, the goal is to get the maximum 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 the condition 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 the condition for the end date, we use the EDATE function to advance one whole 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.
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 day 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 of January 2023 and the first day of 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 .
Explanation
In this example, the goal is to calculate the maximum value of every “nth” column in a row of data, where n is a variable entered in the named range M2 . This problem can be solved in several ways, as explained below. The explanation below also includes a formula that will work in older versions of Excel, before dynamic array formulas were introduced.
FILTER + MOD + SEQUENCE
A classic solution to “nth” problems is to use the MOD function to check a numeric array for a remainder of zero after dividing by n . This is the approach in the worksheet shown, there the formula in cell O5, copied down, is:
=MAX(FILTER(B5:M5,MOD(SEQUENCE(,COLUMNS(B5:M5)),n)=0))
At a high level, the FILTER function is configured to retrieve only the nth values of interest using logic created with MOD and SEQUENCE. The result from FILTER is returned to MAX, which returns the maximum value as a final result. Working from the inside out, the SEQUENCE function is configured to generate a numeric array like this:
SEQUENCE(,COLUMNS(B5:M5))
Because there are 12 columns in the range B5:M5, SEQUENCE returns an 1 x 12 array like this:
{1,2,3,4,5,6,7,8,9,10,11,12}
This array is returned to the MOD function as the number argument, with n provided as the divisor. The result from MOD is then compared to zero:
MOD({1,2,3,4,5,6,7,8,9,10,11,12},n)=0
With the number 3 in cell M2 for n , MOD returns an array like this:
{1,2,0,1,2,0,1,2,0,1,2,0}=0
Note that every 3rd value is zero. When this array is compared to zero, the result is an array of TRUE and FALSE values like this:
{FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}
Notice that every 3rd value is now TRUE. This array is returned directly to the FILTER function as the include argument. With array given as B5:M5, FILTER returns every 3rd value in B5:M5 directly to MAX like this:
=MAX({17,1,8,10})
The MAX function then returns 17 as a final result.
CHOOSECOLS + SEQUENCE
An alternative approach is to use the CHOOSECOLS function with the SEQUENCE function like this:
=MAX(CHOOSECOLS(B5:M5,SEQUENCE(,COLUMNS(B5:M5)/n,n,n)))
This formula calculates the numeric indices of target nth values directly, then requests just those values with the CHOOSECOLS function. The SEQUENCE function is configured to return the numeric indexes of every nth value like this:
SEQUENCE(,COLUMNS(B5:M5)/n,n,n)))
The rows argument is left empty. The columns argument is calculated by dividing the number of columns by n . Both start and step are assigned the value n . With 12 columns in B5:M5, and the number 3 for n , this simplifies to:
SEQUENCE(,4,3,3))
The result is a numeric array of “target nth values” like this:
{3,6,9,12}
This array is returned directly to the CHOOSECOLS function as the second argument:
=MAX(CHOOSECOLS(B5:M5,{3,6,9,12}))
The CHOOSECOLS function then returns the values at these locations in an array to MAX:
=MAX({17,1,8,10})
The MAX function returns 17 as before.
All in one formulas
If desired, you can adapt the formulas above to single, all-in-one formulas based on the BYROW function like this:
=BYROW(B5:M16,LAMBDA(r,MAX(FILTER(r,MOD(SEQUENCE(,COLUMNS(r)),n)=0))))
=BYROW(B5:M16,LAMBDA(r,MAX(CHOOSECOLS(r,SEQUENCE(,COLUMNS(r)/n,n,n)))))
The first formula uses the SEQUENCE and MOD approach, the second formula uses the SEQUENCE and CHOOSECOLS approach. Both formulas will return the max values for nth columns for all rows at once.
Legacy Excel
In older versions of Excel without dynamic array formulas like FILTER and SEQUENCE, you can use an array formula like this:
{=MAX(IF(MOD(COLUMN(B5:M5)-COLUMN(B5)+1,n)=0,B5:M5))}
This is an array formula and must be entered with control + shift + enter in older versions of Excel.
At a high level, this formula uses the IF Function together with the MOD and COLUMN functions to “cancel out” values not in nth columns, then runs MAX on the result. The key is this snippet:
MOD(COLUMN(B5:M5)-COLUMN(B5)+1,n)
The COLUMN function is used to get a set of “relative” column numbers as explained in detail here . The result is a numeric array that starts with the number 1:
{1,2,3,4,5,6,7,8,9,10,11,12}
This array goes into the MOD function as the number argument:
MOD({1,2,3,4,5,6,7,8,9,10,11,12},n)=0
where n is the value to use for “nth”. The MOD function returns the remainder for each column number divided by n . When n = 3, MOD will return an array like this:
{1,2,0,1,2,0,1,2,0,1,2,0}
Note that zeros appear for columns 3, 6, 9 and 12, corresponding to every 3rd column. This array is compared to zero with the logical expression =0 to force a TRUE when the remainder is zero and a FALSE when not. These values go into the IF function as the logical test. The IF function then “filters” results so only values in the original range in nth columns make it into the final array. Other values become FALSE. The result is delivered to the MAX function:
=MAX({FALSE,FALSE,17,FALSE,FALSE,1,FALSE,FALSE,8,FALSE,FALSE,10})
The MAX function automatically ignores FALSE values and returns 17 as a final result.
Max of every other column
To get the maximum value in every other column, you can make a small adjustment to the formula when needed. To get the maximum value in even columns, use the original formula with 2 as n :
=MAX(FILTER(data,MOD(SEQUENCE(,COLUMNS(data)),2)=0))
To get the maximum number in odd columns use 2 for n and compare the result to 1:
=MAX(FILTER(data,MOD(SEQUENCE(,COLUMNS(data)),2)=1))
In older versions of Excel, you can use these generic formulas:
{=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0,rng))} // even columns
{=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1,rng))} // odd columns
These are array formulas and must be entered with control + shift + enter in older versions of Excel.
