How can I calculate the average of a group of numbers between two dates in Google Sheets?

Understanding the Structure and Syntax of AVERAGEIFS

The AVERAGEIFS function is an advanced statistical function in Google Sheets, belonging to the family of “IFS” functions (alongside SUMIFS and COUNTIFS), which are crucial for complex conditional data aggregation. Unlike the simpler AVERAGEIF function, which only handles a single condition, AVERAGEIFS is designed to evaluate multiple conditions simultaneously before determining which values should be included in the final calculation. When applied to dates, this capability is essential because filtering for a range always requires two conditions: one for the start boundary and one for the end boundary.

The syntax for the AVERAGEIFS function requires a specific order of arguments, beginning with the range containing the values to be averaged, followed by pairs of criteria ranges and their corresponding conditions. The general structure is as follows: =AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2, ...). It is critical to note that the average_range must always be listed first. This contrasts with the syntax of SUMIFS or COUNTIFS functions, where the average range or sum range typically appears after the initial criteria checks. For date calculations, both criteria_range1 and criteria_range2 will often refer to the exact same column, specifically the column containing the dates.

To calculate the average between two dates, we must define the lower and upper bounds of the date range using comparative operators enclosed in quotes. For example, to establish a start date (e.g., January 5, 2022), the criterion must be written as ">=1/5/2022". This tells the function to only consider dates that are greater than or equal to that specific date. Conversely, to establish the end date (e.g., January 15, 2022), the criterion is written as "<=1/15/2022". The simultaneous application of both these criteria ensures that only those rows whose dates fall within the specified period are included in the average calculation, thus isolating the desired subset of data accurately.

Setting Up Data for Time-Based Averaging

Before implementing the AVERAGEIFS function, ensuring your data is properly structured and formatted is paramount for success in Google Sheets. The data must typically reside in two adjacent columns: one dedicated to numerical values (the sales, scores, or measurements you wish to average) and another dedicated to the associated date stamps. These date stamps must be recognized by Google Sheets as actual date values, not just text strings. If dates are improperly formatted, the conditional operators (such as >= and <=) will fail to execute the comparison correctly, resulting in an error or an incorrect average of zero.

For the purposes of our detailed example, we will consider a simple dataset tracking daily sales figures over a specific period. Column A will hold the dates, and Column B will hold the corresponding sales amounts. We aim to determine the average daily sales that occurred within a defined time window. Below is the general format of the formula used to achieve this conditional average calculation, specifically targeting the data range from rows 2 through 11 in both columns.

You can use the following formula to calculate the average in Google Sheets only for cells that fall between two specific dates:

=AVERAGEIFS(B2:B11, A2:A11, "<=1/15/2022", A2:A11, ">=1/5/2022")

This particular formula calculates the average value of cells in range B2:B11 where the date in the range A2:A11 is between 1/5/2022 and 1/15/2022.

As seen above, the formula requires us to specify the date range twice: once for the upper bound ("<=1/15/2022") and once for the lower bound (">=1/5/2022"). Both criteria refer back to the date column A2:A11. This structure ensures that the function applies both conditions simultaneously, filtering out any records that fall outside this specific 11-day period before performing the average calculation on the corresponding sales figures in column B.

The following example show  how to use this formula in practice.

Practical Example: Calculating Average Sales Between Two Dates

To illustrate the power and precision of the AVERAGEIFS function, let us apply it to a real-world scenario involving sales data. Imagine a company tracking daily sales totals. Management needs a quick assessment of performance specifically during the first half of January 2022. We must isolate the sales data corresponding to the dates ranging from January 5, 2022, through January 15, 2022, inclusive. This type of conditional analysis is crucial for evaluating short-term campaign effectiveness or seasonal variations.

Our dataset, shown below, spans a larger range of dates, but our goal is to extract only the relevant subset. Notice that the dates in column A are chronological, and the sales figures in column B fluctuate daily. The function’s role is to programmatically scan column A, identify rows that meet the dual date criteria, and then pull the corresponding numerical values from column B for averaging.

Suppose we have the following dataset that shows the total sales made by some company on various dates:

We can use the following formula to calculate the average daily sales between 1/5/2022 and 1/15/2022:

=AVERAGEIFS(B2:B11, A2:A11, "<=1/15/2022", A2:A11, ">=1/5/2022")

The implementation of the formula is straightforward. We input the function into an empty cell (e.g., C2 or D2) within our Google Sheet. The resulting value will dynamically update if any of the underlying dates or sales figures within the defined Range change. This dynamic capability is what makes spreadsheet functions superior to manual data calculation, especially when dealing with high-volume or frequently changing datasets.

Deconstructing the AVERAGEIFS Formula for Date Range Filtering

A precise understanding of each argument within the AVERAGEIFS structure is vital for successful implementation. The formula used in our example, =AVERAGEIFS(B2:B11, A2:A11, "=1/5/2022"), can be broken down into three logical components: the average range, the first criterion pair (end date), and the second criterion pair (start date).

1. Average Range (B2:B11): This is the initial and required argument. It specifies the actual column of numbers that the function will average. Only values in this Range corresponding to rows that satisfy all subsequent criteria will be included. In our case, this is the Sales column.

2. Criterion Pair 1 (A2:A11, "<=1/15/2022"): This pair establishes the upper bound of our time window. A2:A11 is the first criteria range (the Date column), and "<=1/15/2022" is the first criterion. The less than or equal to operator (<=) ensures that any date after January 15, 2022, is immediately excluded from consideration.

3. Criterion Pair 2 (A2:A11, ">=1/5/2022"): This pair establishes the lower bound. Again, the criteria range is A2:A11 (the same Date column), and ">=1/5/2022" is the second criterion. The greater than or equal to operator (>=) ensures that any date prior to January 5, 2022, is excluded. The simultaneous application of both criteria narrows the data pool to only those rows falling precisely within the 1/5/2022 to 1/15/2022 window.

It is a common error to reverse the order of the criteria, but mathematically, the order does not matter as long as both conditions are applied correctly to the date column. However, clarity is improved by listing the criteria pairs logically. Most importantly, remember that both the operator and the date value must be enclosed within double quotation marks (e.g., ">=DateValue") because the criterion argument expects a text string input, even when performing a numerical or date comparison.

Visual Confirmation and Manual Verification of Results

Once the AVERAGEIFS formula is correctly entered into the Google Sheets cell, the result appears instantly. This outcome is a crucial metric, representing the average daily performance across the targeted date range. For critical analysis, especially when first implementing a new formula structure, it is always recommended to manually verify the calculated result against the source data to ensure the formula logic has been interpreted correctly by the software.

The following screenshot shows how to use this formula in practice:

The visual output confirms that the average daily sales between January 5, 2022, and January 15, 2022, is 7.2. To perform a manual check, we must first identify which rows meet both date criteria. Examining the dataset (A2:B11), we find the following qualifying dates and corresponding sales values:

• Date 1/5/2022: Sales 7 (Included, as it is >= 1/5/2022 and <= 1/15/2022)

• Date 1/8/2022: Sales 7 (Included)

• Date 1/10/2022: Sales 8 (Included)

• Date 1/12/2022: Sales 6 (Included)

• Date 1/15/2022: Sales 8 (Included, as it is <= 1/15/2022)

The dates 1/1/2022, 1/2/2022, 1/18/2022, and 1/20/2022 are correctly excluded because they fail either the minimum or maximum date criterion. The total number of included data points is five. The sum of the included sales figures is 7 + 7 + 8 + 6 + 8, which totals 36.

The average daily sales between 1/5/2022 and 1/15/2022 is 7.2.

We can manually verify that this is correct:

Average Daily Sales = (7 + 7 + 8 + 6 + 8) / 5 = 7.2.

Advanced Considerations and Troubleshooting Date Criteria

While the AVERAGEIFS function is robust, it is highly sensitive to input errors, especially those related to date formatting. The single most common issue encountered when averaging data between dates is that Google Sheets might not recognize the input criteria as valid dates. If the criterion ">=1/5/2022" uses a date format different from the underlying data, the function will return an average of zero, indicating that no rows matched the specified criteria. Always ensure your manual date inputs match the expected locale settings (e.g., MM/DD/YYYY vs. DD/MM/YYYY).

For professionals handling very large datasets or requiring criteria derived from external cell references, hardcoding the dates into the formula (as we did with "<=1/15/2022") is inefficient. A more dynamic and flexible approach involves referencing cells containing the start and end dates. To reference a cell (e.g., cell D1 containing the start date), the syntax changes slightly due to the required concatenation operator (&). The formula fragment would look like this: A2:A11, ">="&D1. This method ensures that the date range can be modified instantly by simply updating the dates in cells D1 and D2, without having to edit the long function string itself.

Furthermore, users should be aware of the difference between inclusive and exclusive date ranges. Our example uses the inclusive operators >= (greater than or equal to) and <= (less than or equal to), meaning the specified start and end dates (1/5/2022 and 1/15/2022) are included in the average calculation. If the requirement was to exclude the boundary dates (i.e., calculate the average strictly *between* the dates), the operators would need to be changed to > (greater than) and < (less than). Understanding this subtle distinction is critical for accurate Temporal Analysis.

Conclusion and Reference Documentation

The AVERAGEIFS function stands as an indispensable tool for conditional calculations in Google Sheets, particularly when dealing with chronological data. By mastering the application of dual criteria—one for the start date and one for the end date—users gain precise control over which segments of their data contribute to the final statistical output. Whether analyzing sales trends, project durations, or performance metrics, the ability to accurately calculate averages within defined time bounds is a cornerstone of effective spreadsheet management and data reporting.

Always remember the syntax structure: the range to be averaged first, followed by the pairs of criteria ranges and criteria strings. Pay close attention to date formatting and the proper use of comparison operators (>=, <=) to define inclusive time windows. Utilizing cell references instead of hardcoded dates will ensure maximum flexibility and maintainability of your financial or statistical models in the long run.

Note: You can find the complete documentation for the AVERAGEIFS function.