Mahbubur Rahman
In this article, we will know, how to calculate the average if within a date range. We will use a sample dataset containing Date, Region, and Sales. We want to calculate the average of sales between a certain date range. Let’s see, some methods for Excel to count average if within date range.
Download Practice Workbook
3 Ways to Count Average If within the Date Range in Excel
We will go through three simple and easy methods including AVERAGEIFS, AVERAGE, and SUMPRODUCT functions.
Method 1: Count Average If within Date Range Using AVERAGEIFS Function
The most used formula for average if within a date is AVERAGEIFS. Let’s jump into this method.
Here, we want to calculate the average sales between the dates 22/03/2022 and 29/03/2022.
Steps:
- First, click on the F5 cell and type the following formula.
=AVERAGEIFS(D5:D15, B5:B15,"<=29/03/2022", B5:B15,">=22/03/2022")
- After that, press ENTER key.
This formula will calculate the average number of sales from the date 23/03/2022 to 28/03/2022.
Read More: How to Calculate Date Range in Excel
Method 2: Average with If Condition within Date Range Using AVERAGE Function
Now, we will see another method to calculate the average if within the date range. Suppose, we want to know the average sales from 20/03/2022 to 28/03/2022. To calculate that we are going to use the AVERAGE function along with IF.
Steps:
- First,click on cell F5 and type the following formula.
=AVERAGE(IF((B5:B15>=B5)*(B5:B15<=B13),D5:D15))
- Finally, press the CTRL+SHIFT+ENTER key altogether (only ENTER will be enough if you are an Excel 365 user).
Formula Break down
- Here, IF((B5:B15>=B5)*(B5:B15<=B13),D5:D15) formula will yield the result as {100;200;300;400;500;600;700;800;900;FALSE;FALSE}, where it took all the sale values within our specified range.
- Then AVERAGE({100;200;300;400;500;600;700;800;900;FALSE;FALSE}) will give us the output 500.
Read More: Excel Formula Date Range
Similar Readings:
- How to Use Pivot Table to Filter Date Range in Excel (5 Ways)
- How to Set Due Date Reminder in Excel (3 Quick Methods)
- How to Filter Date Range in Excel (5 Easy Methods)
- How to Filter Date Range in Pivot Table with Excel VBA
- VBA to Pivot Table Filter Between Two Dates in Excel
Method 3: Calculate Average with If Condition by SUMPRODUCT
At the end of this post, we will see the use of the SUMPRODUCT function to average if within a date range in Excel. Here, we will calculate the average of sales from the date 21/03/2022 before the date 28/03/2022.
Steps:
- At first, type the following formula in cell F5.
=SUMPRODUCT(--(B5:B15>=B6),--(B5:B15<B14),D5:D15)/SUMPRODUCT(--(B5:B15>=B6),--(B5:B15<B14))
- Finally, press the ENTER key.
Formula BreakdownÂ
- Here, SUMPRODUCT(–(B5:B15>=B6),–(B5:B15<B14),D5:D15) yields the result 4400Â
- SUMPRODUCT(–(B5:B15>=B6),–(B5:B15<B14)) yields the result 8. That’s why we used a division in our formula which will calculate the average.
Read More: How to Do SUMIF Date Range Month in Excel (9 Ways)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
These are 3 different methods in Excel to average if within date. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- Excel SUMIF with a Date Range in Month & Year (4 Examples)
- How to Use SUMIFS to SUM Values in Date Range in Excel
- Excel VBA: Filter Date before Today (With Quick Steps)
- How to SUMIF between Two Dates and with Another Criteria (7 Ways)
- VLOOKUP Date Range and Return Value in Excel (4 Suitable Methods)
Hi! This is helpful but I am interested to see if you could do average sales in a time period and a county. So let’s say, I want to find the average sales in May in each country. Could you help me with a formula for that?
Hi MKM,
Thanks for your comment. To answer your question, we can use the dataset below. It contains 3 rows of sales for Germany. The first sale happened in March and the other two sales happened in May. From this dataset, we can easily calculate the average sales for May in Germany.
To calculate the average sales for May in Germany, select Cell F5 and type the formula below:
=AVERAGEIFS(D5:D15, B5:B15,”<=5/31/2022", B5:B15,">=5/01/2022″,C5:C15,”Germany”)
Press Enter to see the result.
I hope this will help you to solve your problem. Please let us know your queries if you face any issues.
Thanks.