Excel SUMIF with a Date Range in Month & Year (4 Examples)

result of sumif function with greater than condition for date range month and year

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:

=SUMIF(range, criteria, [sum_range])

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:

dataset for sumif date in range month and year

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:Β 

=SUMIF(range,”=”&finish_date,sum_range)

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)

sumif formula to sum between range in month and year

β‘‘ Then, press Enter

resulty of sumif function for month and year

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:Β 

=SUMIF(range,”<β€œ&finish_date,sum_range)

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)

Sumif function if date range less than month and year

β‘‘ Then, press Enter

result of Sumif function with less than or equal condition

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:

date range less than or equal another month and year


Further 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:Β 

=SUMIF(range,”>”&finish_date,sum_range)

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)

greater than example of sumif date range

β‘‘ Then, press Enter

result of sumif function with greater than condition for date range month and year

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:

greater than or qual to in SUMIF function date range


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:Β 

=SUMIF(range,””,sum_range)

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)

use of sumif function if date range of month and year is empty

β‘‘ Then, press Enter

result of the function for empty date range of month and year

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)

sumiif date range for non-empty cells

After that, press ENTER.

result of sumif with date range in month and year

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:Β 

=SUMIFS(sum_range,range,”>=”&start_date1,range,”<=”&start_date2)

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)

sumifs with multiple date criteria

β‘‘ Then, press Enter

result of sumifs function with multiple criteria

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.


Related Articles

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo