Excel How to Sum Values Between Dates

This tutorial shows how to use the SUMIFS function to sum the amounts for records that fall between two dates. By the end of this tutorial, you will be able to enter the desired date range into two input cells, and have Excel immediately sum the values that fall between these dates, even if the underlying transaction data is not sorted. This works with data in any order.

Video

Walkthrough

Let’s take it one step at a time, and work through the following exercises.

Exercise 1: Using the SUMIFS function

In this first exercise, we’ll just get warmed up with SUMIFS by using a single condition.

Consider the following worksheet:

Let’s say we wanted to sum all of the A transactions. We could write the following formula into cell B7:

=SUMIFS(C11:C20,B11:B20,"A")

• the first argument C11:C20 is the range of numbers to add
• the second argument B11:B20 is the criteria range
• the third argument is the criteria value

We hit Enter and bam:

Now that we have summed all of the A transactions using SUMIFS with a single condition, it is time to make things more interesting by using multiple conditions and comparison operators.

Exercise 2: Using Comparison Operators

Now we’ll use two conditions. Consider the following worksheet:

We would like to sum the Customers column for all Ratings between 4 and 5. So, we write the following formula

=SUMIFS(C11:C24,B11:B24,">=4",B11:B24,"<=5")

• C11:C24 is the column of numbers to add
• B11:B24 is the range for the first criteria
• “>=4” is the first criteria value, greater than or equal to 4 enclosed in quotes
• B11:B24 is the range for the second criteria (which is the same range as the first)
• “<=5” is the second criteria value, less than or equal to 5 enclosed in quotes

We hit Enter and bam:

Now that we understand the SUMIFS basics, and how to use multiple conditions and comparison operators, let’s bring this tutorial home and sum transactions that fall between two dates.

Exercise 3: Using Cell References

Consider the transactions below:

First, we enter the From and To dates into cells C6 and C7:

Then we write the following formula into cell B9:

=SUMIFS(C12:C28,B12:B28,">="&C6,B12:B28,"<="&C7)

• C12:C28 is the column of numbers to add
• B12:B28 is the date range
• “>=”&C6 is the greater than or equal to comparison operator enclosed in quotes and joined to cell C6 by the concatenation operator the ampersand &
• B12:B28 is the date range again
• “<=”&C7 is the less than or equal to comparison operator enclosed in quotes, and joined to the cell reference C7 by the concatenation operator the ampersand &

We hit Enter, and bam:

Now, it is very cool that we can simply enter new date values into C6 and C7 and the formulas will instantly update accordingly.

Conclusion

By using the SUMIFS function, you can easily calculate the sum of values between two dates. Whether you need to sum transactions based on specific criteria or want to include multiple criteria, this function provides a convenient way to perform these calculations.

If you have any suggestions, improvements, alternatives, or questions, please post a comment below!

Sample File

FAQs

Q: Can I use the SUMIFS function with multiple criteria?

Yes, the SUMIFS function allows you to use multiple criteria to filter and sum data based on various conditions.

Q: Is it possible to sum values based on dates falling between two specific dates?

Yes, the SUMIFS function is helpful in summing values within a particular date range. By specifying the criteria using comparison operators, you can easily calculate the sum you need.

Q: Can I use references to cells instead of hardcoding values in the SUMIFS function?

Definitely! You can use cell references instead of entering specific values directly into the function. This allows for flexibility in updating criteria without modifying the formula itself.