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.

Explanation

In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function . However, you can also use the FILTER function with the AVERAGE function , as explained below. Because FILTER can work with ranges and arrays , it is a more flexible solution.

Background study

  • How to use the AVERAGEIFS function
  • FILTER function basic example

AVERAGEIFS Function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria . To apply criteria, the AVERAGEIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1)

In this case, we need to test for only one condition, which is that cells in D5:D16 are not blank . We start off with the avg_range , which contains the prices in C5:C16:

=AVERAGEIFS(price,

Next, we add the range that we need to test, the group values in D5:D16:

=AVERAGEIFS(price,group,

Finally, we add the criteria , which is the not equal to operator (<>), which must be enclosed in double quotes (""):

=AVERAGEIFS(price,group,"<>")

Excluding formulas

The formula above will treat empty strings returned by formulas as not empty. If you have empty strings returned by formulas in the criteria range, you can adjust the criteria like this:

=AVERAGEIFS(price,group,">""")

This version of the formula will treat empty strings returned by formulas as blank.

FILTER function

In the current version of Excel, another approach is to use the FILTER function with the AVERAGE function in a formula like this:

=AVERAGE(FILTER(price,group<>""))

In this formula, we are literally removing values we don’t want to average. The FILTER function is configured to return only values in C5:C16 when cells in D5:D16 are not empty . FILTER returns the 8 values in the data that meet this condition directly to the AVERAGE function:

=AVERAGE({355000;209900;448000;129900;189000;385000;679900;439900})

The AVERAGE function calculates an average and returns a final result of 354,575. FILTER is a more flexible function that can apply criteria in ways that AVERAGEIFS can’t. For more on the FILTER function, see this page .