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.

## ๐ย Download Excel File

Download the following Excel workbook to realize the topic more clearly.

## 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**.

**ย Read More: ****7 Tricks to Apply SUMIFS in Date Range with Multiple Criteria**

## ๐ย Takeaways from This Article

๐ย 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.

## Conclusion

That concludes the discussion for today. These are some convenient methods to average if within date range in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website **ExcelDen** to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.