Average hourly pay per day

To calculate an average pay per day based on logged hours and hourly rates, you can use a formula based on the SUMIFS function. In the example shown, the formula in I5 is: =SUMIFS(data[Total],data[Date],H5)/SUMIFS(data[Hours],data[Date],H5) where data is an Excel Table in the range B5:F16. Note this is a weighted average that takes into account the number of hours logged at each rate on each day.

April 14, 2025 · 1 min · 88 words · Danny Thompson

Table of Contents

All data is in Excel Table nameddatain the range B5:F16.

Note that we want to calculate aweighted averagein this case.

By itself, this formula will return the total Amount for each date.

Excel formula: Average numbers ignore zero

Total hours per date

The next step is to calculate the total hours per date.

This formula will return the total Hours logged for each date.

Why not do the same thing with SUMIFS?

Excel formula: Average top 3 scores

This means we can’t supply a calculation for the sum range in this case.

How to use the SUMIFS function

Excel formula: Average last n rows

Excel formula: Basic average example

Excel SUMPRODUCT function

Excel SUMIFS function