Average by month

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. The second approach is based on the FILTER function and the AVERAGE function. For convenience only, both solutions 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.

AVERAGEIFS function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria. The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

In this problem, we need to configure AVERAGEIFS to average amounts by month using two criteria: (1) dates greater than or equal to the first day of the month, (2) dates less than the first day of the next month. We start off with the average range, which contains the values to average in data (C5:C16):

=AVERAGEIFS(amount,

Next, we need to enter the criteria needed to target the appropriate dates for each month. To make this step easier, the values in E5:E10 are all first of month dates formatted to show an abbreviated month name. 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:

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

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 select the correct dates. The final formula in F5, copied down, is:

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

Roughly translated, the meaning of this formula is "Average the amounts in B6:B16 when the date in C5:C16 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 AVERAGEIFS function. As the formula is copied down, it returns a total for each month 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: we could 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 a simpler and more robust solution.

FILTER with AVERAGE

Another nice way to average by month is to use the FILTER function with the AVERAGE function like this:

=AVERAGE(FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy")))

At a high level, the FILTER function extracts the amounts for a given month, and returns these amounts to the AVERAGE function, which calculates an average. The FILTER function is configured like this:

FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy"))

The first argument, array, is set to amount (C5:C16). The second argument, include, is where most of the work gets done:

TEXT(date,"mmyy")=TEXT(E5,"mmyy")

Here, 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:

{"0122";"0222";"0222";"0322";"0322";"0322";"0422";"0422";"0422";"0522";"0522";"0522"}

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 "0122"

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

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

In this array, a TRUE value indicates dates in B5:B16 that are in the same month and year as the date in E5. As you can see, only the first date occurs in January 2022. The FILTER function uses this array to select only values in data that meet criteria. The result is delivered directly to the AVERAGE function like this:

=AVERAGE({100})

AVERAGE then returns a result of 100. As the formula is copied down, FILTER delivers amounts for each month to the AVERAGE function, which returns a final result.

One nice feature of this formula is that it automatically ignores time values that may be attached to dates, so there is no need to worry about excluding dates on the last day of the month that include time values, as with AVERAGEIFS above. This is because the logic created with the TEXT function only compares month and year values. It would be nice to use the TEXT function inside the AVERAGEIFS formula as well, but the AVERAGEIFS function won't accept an array operation in a range argument.

Display dates as names

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

This allows you to use valid Excel dates in column E (required for the formula) but display them as month names only.

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.