Guide to SUMIF and SUMIFS between Two or More Date Ranges - SUMIF Between Two Dates Excel [2024]

Working with dates in Excel can be tricky, especially when you want to sum up data based on specific date ranges. However, Excel provides functions like SUMIF and SUMIFS that make this task easier.

These functions in Excel help you get the sum of cell values between two date ranges. They calculate the sum of a range of values satisfying the criteria defined by the users.

For example, you can calculate the total amount spent between a given date range. For this, you can use the SUMIF function in Microsoft Excel.

If you are looking for a solution to calculate a certain number representing the amount spent, total sales, people attending an event, or employees joining the organization between a given date range, refer to this article. It is a simple guide to help you understand the use of SUMIF between two dates in Excel.

Download the Example Excel Sheet

Calculating the sum of cell values between given date ranges is quite hard, so I suggest you download the following Excel spreadsheet with the data we will use to demonstrate the SUMIF and SUMIFS functions in this article.

Click Here To Download!

It will help you practice and master these functions. If you have your own dataset ready in Excel, skip downloading the above file.

Explained: The SUMIF function in Excel

The SUMIF function in Excel adds the values based on the range and criteria defined by the users. It is also known as the Excel conditional sum function.

The General Syntax of the SUMIF function is as follows,

=SUMIF(range, criteria, [sum_range])

There are a total of three arguments, the first two of which are required, and the last one is optional.

Let us understand these arguments quickly,

“range” – This is a set of data against which the criteria will be tested
“criteria” – Here, you need to define the condition that every cell in the range argument should satisfy. Criteria can be a number, expression, cell reference, text, or function
“sum_range” – This is an optional argument where you can specify the actual range to sum when the condition is met. If omitted, then the Excel sums the cells defined in the range argument

The function is pretty straightforward, but make sure to replace each argument with the proper information.

How to Use SUMIF for a Given Date Range in Excel

You can use the SUMIF function to find the sum of the cells that satisfy the given date range criteria. Note that the SUMIF function is useful for a single criterion or condition, as mentioned earlier.

Consider the following dataset, which shows revenue generated by sales reps in the very first quarter of the year.

How To Use SUMIF Between Two Dates In Excel - Ultimate Guide

Our task is to calculate the total sales for January 2023.

Here are the steps,

Create a new table as shown below

Select the cell “C11” where we wish to insert the SUMIF function
Type “=SUMIF”
Select the first option from the popup or press “Tab” on your keyboard
Choose the date range “B2:B9” in place of the “range” argument
Press “,” on your keyboard to move to the next argument
In place of the “criteria” argument, enter “1/31/2023” or select the cell reference value as “B11”
Press “,” to move to the next argument
Next, we need to select the “sum_range” by putting “C2:C9” as we have sales made by individuals available in column C
End the formula with a closing parenthesis “)”
Press “Enter” to see the result

The final formula should look like this:

=SUMIF(B2:B9,B11,C2:C9)

By using this formula, Excel will instantly calculate the total sales in January 2023 as $314,692.

Make sure the range selected in the place or range and sum_range argument are the same; otherwise, the function will return incorrect values.

As you may have noticed, the SUMIF function can be used in case of a single date. For a date range, you need to use the SUMIF function.

Explained: The SUMIFS function in Excel

The SUMIFS in Excel is used to find the conditional sum of values based on multiple criteria. In contrast to the SUMIF function, which lets you see the sum of cell values for a single criterion, the SUMIFS function allows you to use multiple criteria or conditions.

The general syntax for the SUMIFS function in Excel is as follows,

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Each argument of the above function needs to be replaced with the proper information as follows:

“sum_range” – It represents the range of cells to be summed. You can calculate the amount spent, sales, number of orders, etc.
“criteria_range1” – This is the first range to be evaluated by the associated criteria
“criteria1” – This is the first condition that must be met to get the sum of all the cells
“criteria_range2” – It is the second group of cells against which your second criteria will be tested
“criteria2” – It is for defining the second criteria with which you wish to find the sum of the cells

You can define any number of criteria or conditions to get the sum of cells using the SUMIF function. There is no limit, and it depends upon the size of your dataset.

The function is a bit confusing for beginners. However, once you get used to it, the SUMIFS is one of the most useful functions in Excel.

How to Use SUMIFS between Two or More Date Ranges

The SUMIFS is a powerful array formula capable of performing multiple calculations based on the criteria defined by the users.

It scans the entire column provided to return the values that match the defined criteria or conditions.

We are about to use two comparison or logical operators in Excel,

“>=” – Greater Than or Equal To
“<=” – Less Than or Equal To

These operators compare the cell values against the criteria defined by the users. In simple words, the “>=” operator ensures values that are greater than the given date, and the “<=” operator ensures values that are less than or equal to the given date.

Let us use the same dataset we did in the previous section. Our task is to get the total sales made by the sales reps between January 14, 2023, and January 20, 2023.

Here are the steps,

Create a new table as shown below
Click on the cell “B15”, where we wish to calculate the sum of the cells
Type “=SUMIFS”
Select the first option from the popup or press “Tab” on your keyboard
In place of the “sum_range” argument, choose “C2:C11” as the sales made by a sales rep is available in Column C
Press “,” on your keyboard to move to the next argument
Now, for “criteria_range1”, we will choose the dates column from B2 to B11 by putting “B2:B11”
Press “,” to move to the next argument
Next, for “criteria1”, you must start with the Greater Than or Equal To operator as “>=“
(Make sure to place this operator between quotation marks “,” otherwise you will get an error at the end)
Then, type “&” and put the first criteria, which is the date “1/14/2023” (January 14, 2023)
(Here, you can either manually type the date or select the cell reference from our new table as “B13”)
Press “,” to move to the next argument
For the second range, “criteria_range2”, we will choose the same date column as “B2:B11”
Press ”,” to move to the next argument
The second criterion will start with Less Than or Equal To operator as “<=”
(Make sure you put the operator in quotation marks “”)
Type “&” and put the end date. We will choose the cell reference from our new table as “B14”
End the formula using “)”
Press “Enter” on your keyboard

After using our example Excel spreadsheet and following the steps above, the final formula should look like below,

=SUMIFS(C2:C11,B2:B11,">="&B13,B2:B11,"<="&B14)

Note that you can repeat the above process for any number of date ranges. Simply put, you can define multiple criteria and conditions to calculate the sum of cell values.

Things to Consider while using the SUMIF and SUMIFS functions

You might encounter some errors, or most probably, the Excel may return a “0” value. In case of errors, check the following things:

Ensure that the value in place of the “criteria” argument is exactly matching one or more values in the “range” argument. Check for extra spaces, correct spelling, and case sensitivity.
Make sure that the data type of the values in the “range” argument matches the data type of values in the “criteria” argument.
Look for any hidden characters or non-breaking spaces which can cause issues.