Whenever there are dates on the datasets you work on, you may frequently find yourself doing calculations based on these dates.
For example, whenever the month has sales information, you may need to be informed about the total sales between two specific dates or the total sales during the weekdays and the weekends.
Allow yourself to know about it quickly through the use of the SUMIFS function.
Just as the name suggests, SUMIFS will let you sum a range that has a basis on specific criteria.
In SUMIFs, you can have multiple conditions specified to only sum the values or cells that can meet every requirement.
Our Tutorial will show everyone how you can use the SUMIFS function to sum the values between two dates.
Our Tutorial will cover the following:
- The sum of all the values for a specific product between two dates.
- The sum of all the values between a couple of dates.
The sum of all the values for a particular product between two dates
Since SUMIFS can let you have multiple conditions used, you may have more criteria added in the same formula.
For example, when you get the same data set, you know the total amount of Laptops sold from March 01 to 30.
You can do this by having another condition added to the formula. Here, other than checking the date, it also checks if the product is a Television or not.
It can give you the result that can match every stated condition.
Here is the formula that can do this:
This formula will check the dates and see if the product is a Television or not.
It will only sum up the value when the three conditions are met.
Like that, you can also have the sum of values between the dates you want to have a particular product excluded.
As an example, whenever you want to have the values from April 1 to 30 summed up for every product except for the mobile, you can have this formula used:
In this formula, you don’t use the not-equal-to-operator (<>) to have the values for the Scanner excluded in the result.
For reiterating this, the formula can have hard-coded data values.
However, once the cell has dates, you can use the formula to refer to that cell.
Here are great examples of how you can sum the values of two dates to include more conditions by tweaking the formula.
SUM up all of the values between two dates
Whenever you have a dataset similar to what is shown here, and you want to know the sales that came in from 1-Jan-2020 and 31-Jan-2020 using the format DD-MM-YYYY for the date.
Here is the formula that can provide you with the sum of the sales between these dates:
The SUMIF function above makes use of five arguments that you can change based on the conditions that you have:
- In the first argument, the range of values (C2:C15) is what you want to add.
- The following two arguments are for the first condition. The date needs to be equal to or more than 01-01-2020. It would be best if you had the criteria and criteria range specified for every requirement.
- The final two arguments are for the second condition. These are criteria range and criteria.
The dates are hard-coded in the formula above.
It can be possible for you to have the dates in the cell and instead use a cell reference.
Besides that, whenever the condition involves using an operator like <> or =, the operator should be in the double-quotes.
Note: You can have any date format that is validly used in the formula.
As an example, I used the date 01-01-2020 in the cells.
However, in the formula, I can have any format used as long as it refers to that date.
For example, I can have January 01, 2020, 1-Jan-2020, or January 01, 2020, used as long as the date format is valid.
Excel can have this used to have the sum of the two given dates calculated.
As an example, when you have the cells’ start and end dates as shown below, you can have this formula used to get the given date range’s sum of sales:
Just remember that the cell reference must be out of the double-quotes, and the operator must be in double-quotes.
We hope that our Tutorial has helped you in this matter!