Excel SUMIF with Date Criteria: Formula Examples
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:
=SUMIF(C6:C15,"6/21/2021",G6:G15)
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):
=SUMIF(C6:C15,B2,G6:G15)
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:
=SUMIFS(F6:F15,C6:C15,">="&B2,C6:C15,"<="&B3)
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:
=SUMIFS(Sheet1!F2:F11,Sheet1!C2:C11,">="&B2,Sheet1!C2:C11,"<="&B3)
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:
=SUMIFS(F6:F15,C6:C15,">="&B2,C6:C15,"<="&EOMONTH(B2,0))
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.
=SUMIFS(F6:F15,C6:C15,">="&B2,C6:C15,"<="&EOMONTH(B2,0))
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:
=SUMIFS(F6:F15,C6:C15,">="&DATE(B2,1,1),C6:C15,"<="&DATE(B2,12,31))
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:
=SUMIFS(F6:F15,C6:C15,B2,D6:D15,"<>")
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!
