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:
Download Your Example Excel File
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.
Step 1: =IFS(C3:C19,B3:B19,”>=”&F5,B3:B19,”<=”&F6)
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).
(add all values from C3 to C19)= 340
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?
=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:
The SUMIFS function would meet conditions based on the match between dates, numbers, values, or even text format arguments within the given parameters.
(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.