How to SUM values across two dates (using the SUMIFS formula)
You would frequently find yourself attempting to do computations based on the dates while working with datasets that contain dates.
For instance, if you have sales information for a month, you could be interested in knowing the total sales between two dates, or total sales made on weekends versus weekdays.
Using the SUMIFS function is one method for getting these results fast.
As the name implies, SUMIFS enables you to sum a range depending on specifications. You may define many criteria in SUMIFs, and it will only total the cells and values that satisfy all the constraints.
In this article, I’ll demonstrate how to use the SUMIFS function to total data between two dates.
then let’s get going!
This instruction explains: 1. SUM all data spanning two dates 2. SUM all values for a certain product between two dates.
1. SUM all data spanning two dates
Assume you have the dataset depicted below and are interested in learning about the sales that occurred between January 1 and January 31, 2020. (I am using the DD-MM-YYYY format for the date here)
The calculation that will provide you with the total sales between these two dates is shown below:
=SUMIFS(C2:C15,A2:A15,">=1-1-2020",$A$2:$A$15,"<=31-01-2020")
The SUMIF method mentioned above takes five arguments (the number of arguments depends on how many criteria you have):
- The range (C2:C15) in the first parameter contains the numbers we wish to add.
- The second and third arguments support the first requirement, which stipulates that the date must be greater than or equal to January 1, 2020. You must define the criterion range and the criteria for each condition.
- The second condition is supported by the fourth and fifth justifications, which are the criteria and the criteria range.
I have hard-coded the dates in the aforementioned algorithm. Additionally, you may save the dates in a cell and refer to that cell instead. Additionally, you must include any operators in double quotes when your condition calls for the use of an operator (such as = or >).
Note:
You may enter any legal date format into the formula, For instance, I entered the date as 01-01-2020 in the cells, but I may use any format that still corresponds to this date in the formula. For instance, I might use January 1, 2020, January 1, 2020, or January 1, 2020. Excel will be able to utilize the date format to compute the total between the two specified dates as long as it is valid.
For instance, you may use the following formula to get the total sales for the specified period range if you have the start date and end date in cells (as shown below).
=SUMIFS(C2:C15,A2:A15,">="&F1,$A$2:$A$15,"<="&F2)
Keep in mind that the cell reference must not be enclosed in double quotations and that the operator must.
2. SUM all values for a certain product between two dates.
You may include additional criteria in the same calculation since the SUMIFS function allows you to employ multiple conditions.
For instance, let’s say you have the same dataset (as seen below), but this time you’re interested in learning the total number of printer sales that occurred between the two specified dates (01 Jan and 31 Jan).
You may do this by including a condition in the formula that, in addition to verifying the date, also determines whether the product is a printer or not. The outcome that satisfies all the requirements will then be presented to you.
The equation to accomplish this is given below:
=SUMIFS(C2:C15,A2:A15,">=1-1-2020",$A$2:$A$15,"<=31-01-2020",$B$2:$B$15,"Printer")
The aforementioned algorithm determines whether or not the product is a printer as well as the dates. Only when all three requirements are satisfied will it sum a value.
The total of values between dates when you wish to exclude a certain product is another option.
For instance, you may use the following calculation to aggregate values between January 1 and January 31, except the Scanner, for all other products:
=SUMIFS(C2:C15,A2:A15,">=1-1-2020",$A$2:$A$15,"<=31-01-2020",$B$2:$B$15,"<>Scanner")
I’ve used the not-equal-to operator (>) in the calculation above to prevent the values for Scanner from appearing in the outcome.
Just to be clear, the data values in the formula are hard-coded, but if you have these dates in a cell, you may use that cell as a reference in the calculation.
These are a few instances of how you can total values between two dates; if you like, you may modify the formula and add more criteria.
