How to Apply AVERAGEIFS Function Between Two Dates in Excel

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))

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 1^st and 2^nd 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.

Average Between Two Dates.xlsx

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.

<< Go Back to Excel AVERAGEIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!