On many occasions in Microsoft Excel, we need to calculate an average of the data between the interval of two dates. For instance, the average cost of groceries for a family in the first week of a month, the average daily sales in the first quarter of a year of a shop, etc. In these situations, we have to apply the AVERAGEIFS function to calculate the average. In this article, I will show you how to calculate AVERAGEIFS between two dates in Excel. Let’s begin.
How to Apply AVERAGEIFS Function Between Two Dates in Excel: 3 Easy Steps
Suppose, we have a list of daily sales of a store like this below.
Now, if we want to calculate the average sales using the AVERAGEIFS function between 01-09-22 to 10-09-22, we have to follow the steps below.
Step 01: Select Cell Where Average Will Be Displayed
- First, select a cell where you want to show the average daily sales from 01/09/22 to 10/09/22. Here, we have selected cell F6.
Step 02: Input Formula in Cell
- Now, input the following formula on cell F6
=AVERAGEIFS(C5:C14,B5:B14,">=01/09/2022",B5:B14,"<=10/9/2022")Here,
- C5:C14 is the range from which the average will be calculated.
- B5:B14 is the range where 1st criteria will be tested.
- “>=01/09/2022” is the 1st criterion which implies dates that are greater than 1st September,2022 will be counted.
- B5:B14 is the range where 2nd criteria will be tested.
- “<=10/9/2022” is the 2nd criterion which implies dates that are less than 10th September,2022 will be counted.
Step 03: Press ENTER Key to Get the Average Value
- Now press the Enter key, and you will get the following result.
- To prevent misunderstanding of the date format, you can use the following formula with the DATE function instead of directly entering the dates.
=AVERAGEIFS(C5:C14, B5:B14, ">="&DATE(2022,9,1), B5:B14, "<="&DATE(2022,9,10))
- Suppose the dates are such that they are starting and ending days of the month. In that case, ( Here from 1/09/2022 to 30/09/2022), you can use the following formula, which includes the EOMONTH function, to calculate the average.
=AVERAGEIFS(C5:C14, B5:B14, ">="&DATE(2022,9,1), B5:B14, "<="&EOMONTH(B5,0))
Read More: How to Use AVERAGEIFS Between Two Values in Excel
Things to Remember
- Remember to put the quotation marks (” “) in the right place while typing the formula; otherwise, you will get an error.
- The sequence of 1st and 2nd criteria in the formula is not mandatory. You can write in your own convenient sequence.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.
Related Articles
- How to Use AVERAGEIFS Function for Multiple Columns
- Excel AVERAGEIFS with Multiple Criteria in Same Range
- AVERAGEIFS for Multiple Criteria in Different Columns in Excel
- How to Use Excel AVERAGEIFS Function with Multiple Criteria
- [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel
- AVERAGEIFS Function with “Not Equal to” Criteria
- Difference Between AVERAGEIF and AVERAGEIFS in Excel
