# 3 Ways to Calculate Average If Date Within Range in Excel

Manually calculating the average between two given dates in a large dataset is inefficient and cumbersome. We can easily enter a simple formula to find the average within a date range. In this article, we will describe how to average if within date range in Excel with 3 convenient approaches.

## Learn to Calculate Average If Date Within Range in Excel with These 3 Suitable Approaches

Here, we will use the following sample dataset to calculate average within a date range in Excel.

Approach

### 1. Using AVERAGEIFS Function

In our first approach, we will use the AVERAGEIFS function to determine the average within a date range.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• In the beginning, type in the Start and End Date in G5 and G6 cells respectively.
• In our case, we used 12 December 2022 as the Start Date and 18 December 2022 as the End Date. We will use these two dates as our date range.
• Next, select cell G7 and Enter the formula below:

=AVERAGEIFS(D6:D14,B6:B14,โ>=โ&G5,B6:B14,โ<=โ&G6)

• Consequently, we have calculated the Average Profit from 12 December 2022 to 18 December 2022.

ย Read More: 9 Easy Ways to Calculate Date Range in Excel

Approach

### 2. Combining AVERAGE and IF Functions

Afterward, we will merge AVERAGE and IF functions to average if within date range.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Initially, we will enter the Start and End Dates in cells G5 and G6.
• Later, type in the following formula in cell G7 and press Enter:

=AVERAGE(IF((B6:B14>=G5)*(B6:B14<=G6),D6:D14))

• Voila! Eventually, our Average Profit from dates 12 December to 18 December 2022 is in cell G7.

๐จ Formula Breakdown

AVERAGE(IF((B6:B14>=G5)*(B6:B14<=G6),D6:D14))

๐ย  The IF function will find dates greater than the date stored in cell G5 and less than the date stored in cell G6 from cells B6:B14.

๐ย  Then, it will return the average of the corresponding Profit from cells D6:D14 with the help of the AVERAGE function.

Note:

• For older Excel versions e.g. 2019, press Ctrl+Shift+Enter to apply the formula in cell G7.
• After that, you will notice a pair of curly brackets around the formula which depicts the formula as an array formula.

Approach

### 3. Applying SUMPRODUCT Function

In our final approach, we will use the SUMPRODUCT function to average if within a date range.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• To begin with, Enter the Start and End Dates in cells G5 and G6 as the date range.
• Afterward, in cell G7, Enter the formula below:

=SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0),D6:D14)/SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0))

• Done! Momentarily, we will get the Average Profit in cell G7.

๐จ Formula Breakdown

SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0),D6:D14)/SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0))

๐ย  (B6:B14>=G5)ย and (B6:B14<=G6) will return two arrays of TRUE and FALSE. They will return TRUE if the dates from cells B6:B14 are greater than the date stored in cell G5 and less than the date stored in cell G6 respectively. +0 is added to convert the boolean arrays to binary arrays of 1s and 0s.

๐ย  Next, the SUMPRODUCT function will sum the Profit from cells D6:D14 where the above conditions are satisfied.

๐ย  The second SUMPRODUCT function will return the number of cells where the conditions are fulfilled.

๐ย  Thus, we will get the Average Profit.

๐ย  Firstly, we learned how to find Average Profit within a date range using the AVERAGEIFS function.

๐ย  Secondly, we demonstrated the combination of AVERAGE and IF functions to average if within date range.

๐ย  Lastly, we showed how to use the SUMPRODUCT function to average if within date range.