Learn how to calculate the sum of values between two dates in Excel using simple SUMIFS formulas. Includes practical examples, cell references, multiple criteria, and solutions for date and time calculations.
How to Sum Values Between Two Dates in Excel (Formula & Examples)
When working with dates in Excel, you may often need to calculate the total sales, expenses, payments, or other values recorded within a specific date range. For example, imagine you maintain a daily sales report and want to find out how much revenue your business generated between 1 January and 31 January.
Calculating these totals manually can take time, especially when your spreadsheet contains hundreds or thousands of records. Fortunately, Excel makes this task simple with the SUMIFS function.
With SUMIFS, you can add values that fall between two dates without manually filtering your data. You can also change the start and end dates whenever you need a different report.
In this guide, you will learn how to sum values between two dates in Excel using practical formulas, examples, and tips.
Understanding the SUMIFS Function in Excel
The SUMIFS function allows you to add numbers that meet multiple conditions. When working with dates, you can use it to include values greater than or equal to a starting date and less than or equal to an ending date.
The basic syntax is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Here is what each argument means:
| Argument | Description |
|---|---|
| sum_range | The cells containing the numbers you want to add. |
| criteria_range1 | The cells Excel checks against your first condition. |
| criteria1 | The condition that determines which records to include. |
| criteria_range2 | An additional range to check, if needed. |
| criteria2 | The condition for the additional range. |
For a date-based calculation, you will typically use two conditions: one for the starting date and another for the ending date.
Sample Data for the Excel Date Range Formula
Let's start with a simple sales report.
Suppose you have the following data in your Excel worksheet:
| Date | Product | Sales |
|---|---|---|
| 01-Jan-2026 | Laptop | 1,200 |
| 03-Jan-2026 | Mobile | 800 |
| 05-Jan-2026 | Keyboard | 300 |
| 08-Jan-2026 | Monitor | 1,500 |
| 12-Jan-2026 | Mouse | 250 |
| 15-Jan-2026 | Laptop | 1,800 |
| 20-Jan-2026 | Mobile | 950 |
| 25-Jan-2026 | Monitor | 1,400 |
| 28-Jan-2026 | Keyboard | 450 |
| 31-Jan-2026 | Laptop | 2,000 |
Assume the dates are stored in cells A2:A11 and sales figures are stored in C2:C11.
Now, let's calculate the total sales between two selected dates.
Sum Values Between Two Dates Using SUMIFS
The SUMIFS function is one of the easiest ways to calculate totals within a date range.
For example, suppose you want to calculate total sales from 5 January 2026 to 20 January 2026.
Use this formula:
=SUMIFS(C2:C11,A2:A11,">="&DATE(2026,1,5),A2:A11,"<="&DATE(2026,1,20))How this formula works:
C2:C11tells Excel which sales values to add.A2:A11identifies the date column.">="&DATE(2026,1,5)includes records from 5 January onwards."<="&DATE(2026,1,20)includes records up to and including 20 January.
Result: 3,250
Excel adds the sales recorded on 5, 8, 12, 15, and 20 January.
This method is useful when you want to calculate sales, expenses, orders, or payments for a particular period.
Sum Values Between Two Dates Using Cell References
Typing dates directly into a formula works, but it is not the most convenient option when you regularly prepare reports.
A better approach is to enter the starting and ending dates in separate cells.
For example:
| Cell | Description | Value |
|---|---|---|
| E2 | Start Date | 05-Jan-2026 |
| E3 | End Date | 20-Jan-2026 |
| E4 | Total Sales | Formula |
Enter this formula in cell E4:
=SUMIFS(C2:C11,A2:A11,">="&E2,A2:A11,"<="&E3)Why use this method?
You only need to change the dates in E2 and E3 to calculate another period. There is no need to edit the formula every time.
For example, if you change the dates to 1 January and 31 January, Excel automatically calculates the total sales for the entire month.
This approach is particularly useful for monthly sales reports, business expense trackers, and financial dashboards.
Sum Values Between Two Dates Using the SUM Function
You can also calculate a date-based total using the SUMPRODUCT function, which evaluates conditions and adds matching values.
For example:
=SUMPRODUCT((A2:A11>=E2)*(A2:A11<=E3)*C2:C11)This formula checks whether each date falls between the starting and ending dates. If both conditions are true, the corresponding sales amount is included in the total.
Although SUMPRODUCT is flexible, SUMIFS is generally easier to understand and maintain for straightforward date-range calculations.
Sum Values Between Two Dates Including Time
Sometimes your Excel data contains both dates and times.
For example:
| Date and Time | Sales |
|---|---|
| 05-Jan-2026 09:30 AM | 500 |
| 05-Jan-2026 02:15 PM | 700 |
| 10-Jan-2026 11:00 AM | 900 |
| 20-Jan-2026 04:30 PM | 1,200 |
| 21-Jan-2026 10:00 AM | 600 |
If you use an ending-date condition such as "<="&E3, Excel may exclude transactions recorded after midnight on the ending date.
To include the entire ending date, use the following formula:
=SUMIFS(B2:B6,A2:A6,">="&E2,A2:A6,"<"&E3+1)This formula includes every transaction from the start date through the end date, regardless of the recorded time.
It is particularly useful for transaction reports, daily sales records, and payment histories.
Sum Values Between Two Dates with an Additional Condition
Excel also allows you to combine date conditions with other criteria.
For example, suppose your sales report contains different products, and you want to calculate the total sales of Laptop products between 5 January and 20 January.
Use this formula:
=SUMIFS(C2:C11,A2:A11,">="&E2,A2:A11,"<="&E3,B2:B11,"Laptop")Here, Excel checks three conditions:
The date must be on or after the starting date.
The date must be on or before the ending date.
The product name must be Laptop.
This formula is helpful when you want to prepare product-wise sales reports, employee-wise payment reports, or category-wise expense summaries.
Common Problems When Summing Values Between Dates
Sometimes a formula returns zero or an incorrect total even when the data appears correct. This usually happens because of formatting or data-entry issues.
Here are some common problems and their solutions.
| Problem | Possible Reason | Solution |
|---|---|---|
| Formula returns zero | Dates are stored as text | Convert text dates into real Excel dates. |
| Some transactions are missing | Date conditions exclude time values | Use <EndDate+1 instead of <=EndDate. |
| Incorrect total | Wrong sum or criteria range | Check that the selected ranges cover the same rows. |
| Formula does not update | Calculation settings are manual | Set calculation mode to Automatic. |
| Unexpected results | Start date is later than end date | Check both date input cells. |
Important tip: Excel stores genuine dates as serial numbers. You can change how a date looks by formatting the cell, but the underlying value must still be a valid Excel date for SUMIFS to work correctly.
Which Formula Should You Use?
The best formula depends on the structure of your spreadsheet and the type of report you want to prepare.
| Your Requirement | Recommended Formula |
|---|---|
| Sum values between two fixed dates | SUMIFS with DATE |
| Sum values using editable date cells | SUMIFS with cell references |
| Include all transactions on the ending date with time | SUMIFS with <EndDate+1 |
| Add another condition, such as product name | SUMIFS with multiple criteria |
| Use more flexible array-based calculations | SUMPRODUCT |
For most everyday Excel tasks, SUMIFS is the recommended option because it is simple, efficient, and easy to update.
Final Thoughts
Summing values between two dates in Excel is straightforward once you understand how date criteria work. The SUMIFS function provides a reliable way to calculate totals for sales, expenses, payments, and other date-based records.
If you regularly prepare reports, use separate cells for your starting and ending dates. This makes your spreadsheet more flexible and saves time when calculating totals for different periods.
Remember to check whether your data contains actual Excel dates or date-and-time values. Choosing the correct formula for your data structure will help you avoid missing transactions and incorrect totals.
Frequently Asked Questions (FAQs)
1. How do I sum values between two dates in Excel?
Use the SUMIFS function:
=SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2)2. Can I use SUMIF instead of SUMIFS for two dates?
SUMIFS is recommended because it supports multiple conditions, including both starting and ending dates.
3. How do I include both the start date and end date?
Use >= for the starting date and <= for the ending date.
4. Why does SUMIFS return zero for my date range?
Your dates may be stored as text instead of genuine Excel dates. Convert them into valid Excel dates and try again.
5. How do I sum values between two dates when the data contains time?
Use this formula to include the entire ending date:
=SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2+1)
COMMENTS