How to Sum Values Between Two Dates in Excel (Formula & Examples)

Learn how to calculate the sum of values between two dates in Excel using simple SUMIFS formulas. Includes practical examples, cell references, multip

How to Sum Values Between Two Dates in Excel (Formula & Examples)

   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:

ArgumentDescription
sum_rangeThe cells containing the numbers you want to add.
criteria_range1The cells Excel checks against your first condition.
criteria1The condition that determines which records to include.
criteria_range2An additional range to check, if needed.
criteria2The 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:

DateProductSales
01-Jan-2026Laptop1,200
03-Jan-2026Mobile800
05-Jan-2026Keyboard300
08-Jan-2026Monitor1,500
12-Jan-2026Mouse250
15-Jan-2026Laptop1,800
20-Jan-2026Mobile950
25-Jan-2026Monitor1,400
28-Jan-2026Keyboard450
31-Jan-2026Laptop2,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:C11 tells Excel which sales values to add.

  • A2:A11 identifies 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:

CellDescriptionValue
E2Start Date05-Jan-2026
E3End Date20-Jan-2026
E4Total SalesFormula

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 TimeSales
05-Jan-2026 09:30 AM500
05-Jan-2026 02:15 PM700
10-Jan-2026 11:00 AM900
20-Jan-2026 04:30 PM1,200
21-Jan-2026 10:00 AM600

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.

ProblemPossible ReasonSolution
Formula returns zeroDates are stored as textConvert text dates into real Excel dates.
Some transactions are missingDate conditions exclude time valuesUse <EndDate+1 instead of <=EndDate.
Incorrect totalWrong sum or criteria rangeCheck that the selected ranges cover the same rows.
Formula does not updateCalculation settings are manualSet calculation mode to Automatic.
Unexpected resultsStart date is later than end dateCheck 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 RequirementRecommended Formula
Sum values between two fixed datesSUMIFS with DATE
Sum values using editable date cellsSUMIFS with cell references
Include all transactions on the ending date with timeSUMIFS with <EndDate+1
Add another condition, such as product nameSUMIFS with multiple criteria
Use more flexible array-based calculationsSUMPRODUCT

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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content