SUMIFS between two dates not working

Be it for budgeting, reporting, or analysis, you can easily sum data within a date range using the SUMIFS formula. SUMIFS is an extended form of SUMIF formula where you can enter multiple criteria.

Syntax

=SUMIFS(values to sum range, date range, >=minimum date, date range, <=minimum range)

Steps

1. Type =SUMIFS(
2. Select or type range reference that includes cells to add $H$3:$H$10
3. Select or type range reference that includes date values you want to apply the criteria against $C$3:$C$10
4. Type minimum date criteria with equoal or greater than operator ">=1/1/2010"
5. Add the date range again $C$3:$C$10
6. Type maximum date criteria with equal or greater than operator "<=12/31/2012"
7. Type ) and press Enter to complete formula

How

SUMIFS function adds values that meet single or multiple criteria. Ability to use criteria with logical operators like greater than or equal (>=) and less than or equal (<=) provides the way of adding values between values.

To filter dates between two dates, we need two criteria and suitable operators. We used ">=1/1/2010" and "<=12/31/2012" criteria to define start and end dates of searching. Search is made on same date range $C$3:$C$10.

=SUMIFS($H$3:$H$10,$C$3:$C$10,">=1/1/2010",$C$3:$C$10,"<=12/31/2012")

Tip: Order of dates do NOT matter. "<=12/31/2012" criteria can be placed as first criteria.

Alternative way to write formula is using cell references instead of static dates. For example; if your minimum date value is at cell K8, then criteria would be written as “>=”&K8.

=SUMIFS($H$3:$H$10,$C$3:$C$10,">="&K8,$C$3:$C$10,"<= "&K9)

One of the most common ways people summarize data in Excel is when they are looking for totals based on date criteria – let’s say, to sum values with a specific date, between a certain date range, etc. This article provides various examples of how to sum in Excel by date criteria using SUMIF and SUMIFS.

How to use SUMIF(S) in Excel with date criteria

We can use both Excel SUMIF and SUMIFS functions to sum values based on date criteria. And here are some important notes when using these functions:

• You can use either SUMIF or SUMIFS if you want to sum by a single criterion. For example, to sum if the date is equal, before, or after a specific date.
• Use SUMIFS if you want to sum by multiple criteria, such as to sum if the date is between a certain range.
• Be sure to enclose the date criteria within double quotes (“”).

For the basic usage of SUMIF and SUMIFS, we’ve already covered that in our previous tutorial: Excel SUMIF function and how to use it. So, if you want a refresher or to see more examples on how to use Excel SUMIF with text criteria or number, feel free to check it out.

Example case

Suppose you are a project coordinator and use the following Contractor Time Tracker spreadsheet to manage tasks, the contractors who work on them, whether each task is completed or not, as well as how much money has been billed for each particular task. 

What if your data is stored in an external source such as Airtable or Jira (or other platforms), but you want to do analysis using Excel? In this case, you can import them first into Excel then do an analysis with it. We recommend checking out Coupler.io for an easy way to manage this process! Coupler.io is an integration tool that allows you to import data from multiple sources into Excel and sync your data on the schedule you want — without coding. 

Excel SUMIF date with single criteria examples

Let’s check out some examples below on how to sum values based on a single criterion using the SUMIF function.

Excel SUMIF: date equals to

In the example below, we are adding up how much was billed for all the tasks that started on June 21, 2021. 

The SUMIF formula we use in B3 is:

The formula sums the amounts in column G (range G6:G15) when the date in column C (range C6:C15) is equal to June 21, 2021. Notice that the date criteria is enclosed within double quotes (“6/21/2021”). If it’s not, the formula will return an incorrect result.

You can also use B2 as a cell reference instead of typing the date criteria manually. To do that, just replace “6/21/2021” with B2 (without double quotes): 

Excel SUMIF: date less than, less than or equal to

In the following example, we calculate how much was billed for tasks that started before April 15, 2021, and on or before April 15, 2021.

Here are the formulas we use in B3 and C3:

• Before April 15, 2021 (B3): =SUMIF(C6:C15,"<4/15/2021",G6:G15)
• On or before April 15, 2021 (C3): =SUMIF(C6:C15,"<="&DATE(2021,4,15),G6:G15)

Notice that we use the < operator for less than and <= for less than or equal to. The formula in C3 shows that we can also use the DATE function in the criteria.  

Excel SUMIF: date greater than, greater than or equal to

The following example sums the total bill for the tasks that started after April 15, 2021, and on or after April 15, 2021.

Here are the formulas we use in B3 and C3:

• After April 15, 2021 (B3): =SUMIF(C6:C15,">4/15/2021",G6:G15)
• On or after April 15, 2021 (C3): =SUMIF(C6:C15,">=4/15/2021",G6:G15)

Notice that we use the > operator for greater than and <= for greater than or equal to.  

Excel SUMIF: date is empty, not empty

The following example shows how to add the total bill for tasks that are not finished yet (finish dates are blank) as well as completed ones (finish dates are not blank). 

Here are the formulas we use in B3 and C3:

• Finish dates are empty (B3):  =SUMIF(D6:D15,"",G6:G15)
• Finish dates are not empty (C3): =SUMIF(D6:D15,"<>",G6:G15)

Notice that we use the double quotes without any space between ("") to find the dates that are blank or empty. To find dates that are not empty, we use the not equal operator enclosed within double quotes ("<>").

Excel SUMIFS with multiple date criteria examples

Now, let’s see some examples of how to sum values based on multiple criteria using the SUMIFS function.

Excel sum if date between two dates (date range)

The following example sums the total hours spent for tasks that started between April 15, 2021 and June 21, 2021.  

Here’s the formula in E3:

It adds up values in the Billable hours column but only includes rows where start dates >= B2 and start dates <= B3, where B2 and B3 refer to "4/15/2021" and "6/21/2021".

Excel sum if date range from another sheet

What if you want to sum the values that are in another sheet? For example, you want to get the total billable hours for the data in Sheet1, but your formula is in another sheet, as these two screenshots show:

Sheet1:

Summing the total hours from another sheet — here’s the formula in E3:

Notice that, in the above case, you just need to add the sheet name followed by an exclamation point (Sheet1!) for the range of cells you want to sum and the range that contains the criteria.

Excel sum if date in a specific month

The following example shows how to get the total hours for tasks that started in April 2021.

Here’s the formula in B3, which uses a SUMIFS function with two criteria — start dates >= first day of April 2021 AND start dates <= last day of April 2021:

To get the above formula, you can follow the steps below:

Step 1: Type the first date of April 2021 in B2.

Step 2: Change the format of B2 to mmmm to display the month name.

Step 3: Write the following formula in B3, then press Enter. Note: The EOMONTH function helps you to find the last day of the month.

Excel sum if date in a specific year

The following example shows how to calculate the total hours for all the tasks that started in 2020:

Formula in B3:

The above formula uses SUMIFS with these two criteria: start dates >= January 1, 2020 AND start dates <= December 31, 2020. 

Excel sum if date criteria in multiple columns

The following example shows how to get the total hours for all the completed tasks that started on April 15, 2021. 

Formula in E3:

Notice that in this case, we use SUMIFS with criteria in two different columns: Start date and Finish date. The first criteria is for the start dates that are equal to April 15, 2021, and the second criteria is for the finish dates that are not empty.

Wrapping up

We’ve covered how to use Excel’s SUMIF and SUMIFS functions with date criteria, including common examples on single and multiple criteria. These two functions are simple yet powerful to use in your day-to-day data analysis.

And don’t forget to give Coupler.io a try. With this integration tool, importing data from your apps into Excel has never been easier!