Use Excel SUMIFS() With a Date Range (Before, After, Between)

The Excel SUMIF() and SUMIFS() functions can be used to add values based on a date range, such as between two dates, before a date, and after a date. Being able to add values within date ranges is an important skill in many different industries. This is especially true for finance, where you may need to add values belonging to a particular fiscal period.

In this tutorial, you’ll learn how to use the Excel SUMIFS() function with a date range, such as when dates are between dates or before and after a date. By the end of this tutorial, you’ll have learned the following:

How to use the Excel SUMIFS() function to add values in a date range
How to use the Excel SUMIF() function to add values before or after a provided date
How to use the Excel SUMIFS() function to sum values in a dynamic range based on today’s date

Table of Contents

Excel SUMIFS() With a Date Range

In this section, you’ll learn how to use the Excel SUMIFS() function to sum values that fall in a date range. Because the Excel SUMIFS() function allows us to add values based on one or more conditions, we can use this to check for dates that are larger than one date and smaller than another.

We can use the following formula to sum values in a date range:

=SUMIFS(value_range, date_range, ">="&start_date, date_range, "<"&end_date)

You can see in the code block above that we are using the SUMIFS() function with two conditions. Let’s break this down a little bit further by looking at a practical example:

How to use Excel SUMIFS() with a date range

In the example above, we used the following arguments:

The sum_range is equal to the values in range C3:C13,
The criteria_range1 is equal to the date range in B3:B13,
The criteria1 is equal to ">="&F2, which indicates that we are looking for values that are greater than or equal to the date in cell F2,
The criteria_range2 is equal to the date range in B3:B13,
The criteria2 looks for cells that are less than the date in cell G2

Let’s extend what you learned here and use Excel to add values in a date range as well as meeting another condition, such as being from a particular region.

Excel SUMIFS() With a Date Range and Another Condition

In this section, you’ll learn how to use the Excel SUMIFS() function to sum values in a given date range as well as meeting another condition. There are many similarities between this example and the previous one. Because of this, we won’t dive too much into the details of how the date range calculation works. Instead, we’ll focus on adding our third condition.

In the example below, the dataset has one additional column: the Region in which a sale occurred. We want to add values that are between April 1, 2023, and May 1, 2023, and are in the North region. In order to do this, we can use the SUMIFS() function and simply add one further condition. Let’s take a look at what this looks like:

How to use Excel SUMIFS() with a date range and equal to another value

Let’s see what the function looks like to add values in a date range and meet another condition:

=SUMIFS(value_range, date_range, ">="&start_date, date_range, "<"&end_date, C3:C13, G3)

In the code block above, we re-used the function we had before. We also added one final criteria range and criteria pair. The function now also checks whether or not the values in the Region column (range C3:C13) are equal to the value in cell G3, which in this case is North.

Excel SUMIF() Before a Specific Date

In order to use Excel to add values that occur before a specific date, we can use the simplified SUMIF() function. The Excel SUMIF() function allows us to add values that meet a single condition, even if those values are in a different column. In order to do this, we can use the SUMIF() function and pass in the date range as the criteria range, the condition of "<"&date, and the values as our sum range.

Let’s take a look at an example of what this looks like:

How to use Excel SUMIF() to sum values before a date

We use the following formula to sum values before a specific date:

=SUMIF(B3:B13, "<"&F2, C3:C13)

Note that we’re wrapping the less than operator in double quotes and then concatenating the date to it. This allows us to avoid any errors. Because Excel uses numbers to store dates, this process is actually the same as using the SUMIF() function to add values less than a certain value.

In the following section, you’ll learn how to use the Excel SUMIF() function to add values after a specific date.

Excel SUMIF() After a Specific Date

Similar to using Excel to add values that occur before a specific date, we can use the SUMIF() function to sum values after a specific date. In fact, this only involves modifying the operator from a less than to a greater than operator. Let’s take a look at how we can do this in Excel:

How to use Excel SUMIF() to sum values after a date

We use the following formula to sum values after a specific date:

=SUMIF(B3:B13, ">"&F2, C3:C13)

Similar to the previous example, we added values in the Amount column based on a condition in the Date column. In this case, we used the greater than operator and concatenated it with the date in cell F2. Again, because Excel stores dates as numbers, we were able to use the same process as if we were to sum values greater than a specific value.

In the final section below, you’ll learn how to use the SUMIF() function with the TODAY() function to sum values relative to today’s date.

Excel SUMIF() Dynamic With Today’s Date

The Excel TODAY() function is used to get the current date. Because of this, we can combine the function with what you have learned about adding values in date ranges to sum values relative to today’s date. For example, we can use this to calculate values that fall within the last week.

Let’s take a look at the formula we’ll be using to sum values within the last week:

=SUMIFS(D3:D13, B3:B13, ">"&TODAY()-7, B3:B13, "<="&TODAY())

Because Excel stores dates as numbers, we can subtract dates from any date. Because of this, TODAY()-7 evaluates to the date from seven days ago.

Let’s now take a look at an example of what this looks like in Excel:

How to use Excel SUMIFS() to add values within the last week

In the example above, we add up values that are greater than seven days ago and less than or equal to today. Because Excel will reevaluate this function, the date range will become dynamic as the date changes.

Conclusion

In this tutorial, you learned how to use the Excel SUMIF() and SUMIFS() functions to sum values in a date range. Because both functions allow you to add values based on one or more conditions, we can use dates as our conditions. You first learned how to use the SUMIFS() function to add values in a given date range, as well as adding values in a date range and meeting another condition.

Then, you learned how to add values that fall before or after a given date, using the simpler SUMIF() function. Because in these cases, we’re evaluating only a single condition, the SUMIF() function is sufficient. Finally, you learned how to combine the SUMIFS() function with the TODAY() function to add values relative to today’s date. You learned how to do this by adding values that occurred in the last week.

Additional Resources

To learn more about related topics, check out the resources below: