A.N.M. Mohaimen Shanto
Working with date-based data is one of the most common uses of Microsoft Excel. We perform various calculations based on dates. We use various functions with dates in them to analyze a dataset. In this tutorial, you will learn how to use the SUMIF function with date range criteria in month and year in Excel with 4 suitable examples.
Download Practice Workbook
Download this practice workbook.
Introduction to the SUMIF Function
Objective:
The SUMIF function sums the values in a range that meets the criteria you specify. It adds the range of cells only if the conditions are fulfilled.
Syntax:
Arguments:
range: Required. The range of cells that you want to be evaluated by criteria.
Criteria: Required. The criteria are in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.
[sum_range]: Optional. The actual cells to add if we need to combine cells other than those defined in the range argument.
4 Examples of Excel SUMIF with Date Range Criteria in Month and Year
To demonstrate this tutorial, we are using this dataset:
Here, we have a dataset consisting of some projects, their start date, and finish date. We are going to work with these dates along with the SUMIF function.
Now, we will demonstrate four examples of the SUMIF function with criteria range in month and year.
Example-1: Excel SUMIF with Date Range Equal to a Month & Year
Here, we are using the SUMIF function to add the total Bill of the projects if the finish date is equal to something.
The Generic Formula:
We are going to find the total bill of the projects finished in December 2021
📌 Steps
① First, type the following formula in Cell C13:
=SUMIF(E5:E10,"="&C12,H5:H10)
② Then, press Enter
As you can see, we are successful in finding the total bills of the projects finished on December 21.
Here, the SUMIF function finds the finish date Dec-21, and after that, adds the total bill according to that.
Example-2: Excel SUMIF with Date Range Less Than or Equal to a Month & Year
If you want to add total bills finished before a particular date, this method will work at ease. We are performing this method with the less than (“<”) sign. You can also do this by the less than or equal sign (“<=”).
The Generic Formula:
We are going to find the total bill of the projects finished before December 2021
📌 Steps
① First, type the following formula in Cell C13:
=SUMIF(E5:E10,"<"&C12,H5:H10)
② Then, press Enter
As you can see, we are successful in finding the total bills of the projects finished before December 21.
Note: To perform this operation with less than or equal (“<=”) sign, just change the sign in the formula like shown below:
Similar Readings:
Example-3: Excel SUMIF with Date Range Greater Than or Equal to a Month & Year
Now, if your condition demands to add values based on a date that is greater than another date, this formula should be your go-to method. Greater than means a date occurred after a particular date.
We are performing this method with the greater than (“>”) sign. You can also do this by the greater than or equal (“>=”) sign.
The Generic Formula:
We are going to find the total bill of the projects finished after October 2021
📌 Steps
① First, type the following formula in Cell C13:
=SUMIF(E5:E10,">"&C12,H5:H10)
② Then, press Enter
As you can see, we successfully found the total bills of the projects finished after October 21.
Note: To perform this operation with less than or equal (“>=”) sign, just change the sign in the formula like shown below:
Example-4: Excel SUMIF when Date Range is Empty
There may be a situation where the date is empty in your dataset. Sometimes it means the project is scheduled yet. You can solve it by this method.
The Generic Formula:
We are going to find the total bill where projects are not completed.
📌 Steps
① First, type the following formula in Cell C12:
=SUMIF(E5:E10,"",H5:H10)
② Then, press Enter
As you can see, we are successful in finding the total bills of the unfinished projects.
For Non-empty Cell:
Now, if you want to add the total bill of the projects that are finished, type the following formula in Cell C12:
=SUMIF(E5:E10,"<>",H5:H10)
After that, press ENTER.
Finally, you can see the total bill of the finished projects.
Multiple Date Range in Month & Year: Sum with Excel SUMIFS Function
In some cases, the SUMIFS function comes in handy when you are working with multiple date criteria. It becomes an essential tool while working with multiple ranges of dates. When your date is between specific dates, this formula will work smoothly to give you desired values.
The Generic Formula:
We are going to find the total bill of the projects which finished between January 21 to June 21
📌 Steps
① First, type the following formula in Cell C13:
=SUMIFS(H5:H10,D5:D10,">="&C12,D5:D10,"<="&C13)
② Then, press Enter
Finally, we are successful in finding the total bills of the projects finished between January 21 to June 21.
💬 Things to Remember
✎ We are using these dates from the starting of the month. Such as 01/01/21 is shown as Jan-21.
✎ If you change the date 01/01/21 to 31/01/21, still it will show Jan-21
✎ To work with full date values, change the date format from the Number group in the Home tab.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the SUMIF function with date range month and year in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
