Explanation

In this example, the goal is to calculate the average call time (duration in minutes) for each month listed in column G using the dates in column B and the durations in column E. The article below explains two approaches. The first formula is based on the AVERAGEIFS function , which is designed to calculate averages using multiple criteria. The second formula is based on the FILTER function and the AVERAGE function . For convenience, all data is in an Excel Table named data in the range B5:E16.

Note: the values in G5:G8 are valid Excel dates . This makes it easier to use these values in the formula criteria. You can use a custom number format to display these dates any way you like.

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,...)

Notice each condition is entered as a separate [ range , criteria ] pair. 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 is the call durations column in the table:

=AVERAGEIFS(data[Duration],

Next, we need to enter the criteria needed to target the right dates for each month. To make this step easier, the values in G5:G8 are entered as “first of month” dates. For the start date, we use the data[Date] column for the criteria range and the greater than or equal to operator (>=) concatenated to cell G5 for the criteria:

=AVERAGEIFS(data[Duration],data[Date],">="&G5,

For the end date, we again use the data[Date] column for the criteria range:

=AVERAGEIFS(data[Duration],data[Date],">="&G5,data[Date],

For criteria , we use the EDATE function to return the first day of the next month :

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

The final formula in H5, copied down, is:

=AVERAGEIFS(data[Duration],data[Date],">="&G5,data[Date],"<"&EDATE(G5,1))

Notice we need to concatenate the dates to logical operators , as required by the AVERAGEIFS function. The structured references behave like absolute references and don’t change, while the reference to G5 is relative and changes at each new row. As the formula is copied down, it returns an average call duration for each month in column G.

FILTER with AVERAGE

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

=AVERAGE(FILTER(data[Duration],TEXT(data[Date],"mmyy")=TEXT(G5,"mmyy")))

Working from the inside out, the FILTER function extracts the durations for a given month and returns these amounts to the AVERAGE function, which calculates an average. The FILTER function is configured like this:

FILTER(data[Duration],TEXT(data[Date],"mmyy")=TEXT(G5,"mmyy"))

The first argument, array , is set to data[Duration] . The second argument, include , is where most of the work gets done:

TEXT(data[Date],"mmyy")=TEXT(G5,"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, all in 2023, the result is an array with 12 values like this:

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

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

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

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

{TRUE;TRUE;TRUE;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 G5, i.e. January dates. The FILTER function uses this array to retrieve only values in January. The result is delivered directly to the AVERAGE function like this:

{0.0111111111111111;0.0118055555555556;0.0173611111111112}

Excel times are just fractional parts of 1 day , so the decimal numbers represent time in a raw number format. AVERAGE then returns a result of approximately 0.01343, which, when formatted with the custom number format “mm:ss”, displays as 19:20. As the formula is copied down, FILTER delivers durations for each month to the AVERAGE function, which returns a final result.

Pivot Table solution

A pivot table is an excellent solution when you need to summarize or average data by year, month, quarter, and so on, because pivot tables provide controls for grouping dates automatically. 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 average hourly rate per day based on the data shown. All data is in Excel Table named data in the range B5:F16. Note that we want to calculate a weighted average in this case. A weighted average hourly rate is the average rate at which the workers are paid, taking into account the number of hours worked at each rate. This weighted average is calculated by multiplying each rate by its corresponding weight (hours worked) for a given date, then dividing by the sum of the hours logged on that date. The solution shown requires four general steps:

  1. Create an Excel Table called data
  2. Create a summary table for results, like H4:I7 in the example
  3. Enter the formula and copy it down the summary table

Create the Excel Table

One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed. In this case, all we need to do is create a new table named data with the data shown in B5:D16. You can use the keyboard shortcut Control + T .

Video: How to create an Excel table

The table will now automatically expand or contract as needed when new data is added or removed.

Total amounts per date

Because we have the total for each entry in column F, we already have rates * hours. This means we can just use the SUMIFS function (the SUMIF function would work fine too) to get a total for each date with a formula like this:

SUMIFS(data[Total],data[Date],H5)

The sum_range is the Total column, the criteria_range is the Date column, and the criteria itself is the date in cell H5. By itself, this formula will return the total Amount for each date.

Total hours per date

The next step is to calculate the total hours per date. To do this, we can again use the SUMIFS function in a very similar formula:

SUMIFS(data[Hours],data[Date],H5)

In this formula, the sum_range is the Hours column, and the criteria_range and criteria are the same as the formula above. This formula will return the total Hours logged for each date.

Divide amounts by hours

The last step is to divide the total amount per date by the total hours per date like this:

=SUMIFS(data[Total],data[Date],H5)/SUMIFS(data[Hours],data[Date],H5)

In cell I5, the formula is solved like this:

=SUMIFS(data[Total],data[Date],H5)/SUMIFS(data[Hours],data[Date],H5)
=518/29
=17.86

SUMPRODUCT alternative

As mentioned above, because we have hours * rate already in column F as Amount, we can use this value directly in the SUMIFS formula as shown. However, in cases where the data does not contain hours * rate, you can use the SUMPRODUCT function to run this calculation inside the formula like this:

=SUMPRODUCT((data[Date]=H5)*data[Hours]*data[Rate])/SUMIFS(data[Hours],data[Date],H5)

This formula returns the same result, but does not need the Amount in column F to be part of the data. Why not do the same thing with SUMIFS? It’s a bit technical, but the reason we can’t take the same approach with SUMIFS is that SUMIFS is in a group of eight Excel functions that require a range. This means we can’t supply a calculation for the sum range in this case.