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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Easy Steps to Apply AVERAGEIFS Function Between Two Dates in Excel

Suppose, we have a list of daily sales of a store like this below.

How-to-Calculate-AVERAGEIFS-Between-Two-Dates-in-Excel

Now, if we want to calculate the average sales 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.

How-to-Calculate-AVERAGEIFS-Between-Two-Dates-in-Excel

Read More: How to Use Excel AVERAGEIFS Function with Multiple Criteria


Step 03: Press ENTER Key to Get the Average Value

  • Now press the Enter key, and you will get the following result.

How-to-Calculate-AVERAGEIFS-Between-Two-Dates-in-Excel

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

How-to-Calculate-AVERAGEIFS-Between-Two-Dates-in-Excel

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.

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. Finally, please visit ExelDemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo