Average or Sum between two Dates or Value

Let’s understand ways to find the average or sum of a range of numbers in a column between two dates or times in Excel. We can simply implement the same step to COUNTIFS numbers between two dates.

QUERY

In this example, we have columns Payment date and Items quantity. Our task is to get an average of items quantity in a column between two dates which are equal to or more than the start date but less than or equal to the end date.

Formula to implement:

=AVERAGEIFS(C3:C19,B3:B19,”>=”&F5,B3:B19,”<=”&F6)

Download Your Example Excel File

Average based on two dates
Average values between two dates

Functions applicable:

The AVERAGEIFS function in excel is a plural form of AVERAGEIF. Returns an average (arithmetic mean) number based on one or multiple given criteria.
   

The AVERAGEIFS function has the following arguments:

AVERAGE_ RANGE: The range of cells you actually want to average if it satisfies the condition.

CRITERIA_ RANGE 1, CRITERIA_ RANGE 2, ….:  1 to 127 ranges to be checked against the specified criteria. Criteria_ range1 is required, the following ones are optional.

CRITERIA 1, CRITERIA 2, ……..: 1 to 127 criteria that determine which cells to average. The criteria can be provided in the form of a number, logical expression, text value or cell reference. Criteria 1 is required, additional criteria are optional.

Formula Explanation (Step by Step)

Excel start calculating innermost parenthesis than work outward. You can also debug formula using shortcut F9 function key under formulas tab.

Let’s break the formula into multiple small steps to understand its application.

Formula

Step 1: =IFS(C3:C19,B3:B19,”>=”&F5,B3:B19,”<=”&F6)

Result:

It checks both the criteria i.e. dates which are greater than or equals to date 1 or less than equals to given date 2 to know the average. Return count of TRUE incidents i.e. 17 (number dates between two dates).

Step2: =AVERAGEIFS(C3:C19,B3:B19,”>=”&F5,B3:B19,”<=”&F6)

Result: 20

(add all values from C3 to C19)= 340

(340/17)= 20

It first sums the numbers of items quantity in the given range (C3 to C19) and then divide it after date counts (17) in the column.

Sum If between two dates

Suppose you have a list of dates to get an addition between dates we will use the SUMIFS function that adds a range of cells based on more than one criterion. You will understand, how to calculate the SUM between two dates or a range of numbers in Excel?

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Query: We have two-column time orders written and total time. Our task is to sum the total time given start and end date of Order time.

Formula to implement: 

=SUMIFS(C3:C24,B3:B24,”>=”&$F$7,B3:B24,”<=”&$F$8)

Add numbers based on Start and End dates and time.
Add numbers based on Start and End dates and time.

The SUMIFS function would meet conditions based on the match between dates, numbers, values, or even text format arguments within the given parameters.

Result: 10105

          (622 to 260)

It will return the sum of the given range (C3:C24) based on the condition if a date or a value lies between one or multiple ranges of date or numbers.

Similarly, you can use Excel COUNTIFS function to find Count values falling between two dates.

Note: & operator in Excel is a type of concatenate which is used to join two strings, values or results.