How to sum corresponding values with same date in Excel?
When working with Excel datasets that include date entries with duplicate values, you may find yourself needing to summarize the quantitative data associated with each date. For example, suppose you manage a set of order records as shown below in range A1:B13, where column A lists dates (with some dates appearing more than once), and column B records the associated number of orders. If you want to sum the total number of orders for each unique date to generate a summarized result, Excel provides several methods to accomplish this task efficiently and accurately. This functionality is invaluable for summarizing daily sales, consolidating log records, or any scenario where you need to aggregate data based on identical time points. Choosing the right method can save you considerable manual effort and help ensure data accuracy.
- Sum corresponding values based on same date with formula
- Sum corresponding values based on same date with Kutools for Excel
- Sum corresponding values based on same date with VBA code
- Sum corresponding values based on same date with Pivot Table
Sum corresponding values based on same date with formula
One of the most direct solutions is to use Excel’s SUMIF function. This approach is especially suitable when you want to create a dynamic summary that automatically updates as your source data changes. The formula approach can be applied easily and works well for data ranges that are not excessively large. Please proceed as follows:
1. Enter the following formula in a blank cell where you want the total for a specific date to appear (for example, cell E2):
=SUMIF($A$2:$A$13,D2,$B$2:$B$13)
In the above formula:
- A2:A13: the range containing dates to evaluate
- B2:B13: the range with the values to sum
- D2: the "criteria" cell that supplies the date for which you want the summed value
2. Drag the fill handle down from the cell where you entered the formula to apply it for all unique dates below. This will populate a summary column where each cell displays the total summed value for its corresponding date. See screenshot:
Tips and Reminders:
- If you add new data to your original range, ensure your formula ranges (A2:A13, B2:B13) are updated accordingly.
- If you use structured tables, reference the table field names for more flexibility (e.g.,
=SUMIF(Table1[Date], D2, Table1[Amount])). - If your data contains errors (such as text entries or empty cells), verify source cell contents to ensure accurate summing.
- This approach is best for small to medium-sized datasets; very large datasets may benefit from a Pivot Table for faster summarization.
- If you use the UNIQUE function (Excel365/2021+) in D2 to list distinct dates, use:
=UNIQUE(A2:A13), then apply the SUMIF formula alongside.
Sum corresponding values based on same date with Kutools for Excel
If you have Kutools for Excel installed, the Advanced Combine Rows tool can make this task much more straightforward. This solution lets you consolidate rows by any key field (in this case, dates), and automatically sum the corresponding values, saving you time and reducing the potential for formula errors. It's particularly useful for users who frequently need to summarize large ranges or perform repeated combine-and-calculate operations.
After installing Kutools for Excel, please proceed as follows:
1. Select the data range that you want to sum by the same date. (It is recommended to make a backup of your original data before proceeding, as this tool will transform your data layout.)
2. Go to Kutools > Merge & Split > Advanced Combine Rows.
3. In the dialog box, select your Date column and set it as Primary Key. Next, select the column to be summed (such as "Order") and set its operation to Sum under the Calculate section. This setup tells Kutools to group rows by identical dates and combine their numerical values by summing them.
4. Click Ok. Kutools will instantly produce the summarized list, so that for each unique date, all corresponding amount values are summed in a single row. See below:
 |  |  |
Sum corresponding values based on same date with VBA code
For users seeking an automated or programmable approach, VBA (Visual Basic for Applications) offers a flexible way to sum values by date and export the summary to a new range. VBA is ideal for handling repeating tasks, processing large datasets, or integrating the summarization into a broader workflow—especially when the built-in formulas or features aren't sufficient for your needs.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the window, click Insert > Module and paste the code below into the module:
Sub SumValuesByDate()
Dim SourceRange As Range
Dim OutputRange As Range
Dim Dict As Object
Dim Cell As Range
Dim iRow As Long
Dim LastRow As Long
Dim ws As Worksheet
Dim kDate As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt for source data range containing dates and values
Set SourceRange = Application.InputBox("Select the source data range (dates in first column, values in second):", xTitleId, ws.Range("A2:B13").Address, Type:=8)
If SourceRange Is Nothing Then Exit Sub
' Prompt for output location
Set OutputRange = Application.InputBox("Select the cell to output summary (top-left cell):", xTitleId, "E1", Type:=8)
If OutputRange Is Nothing Then Exit Sub
Set Dict = CreateObject("Scripting.Dictionary")
' Loop through data and sum by date
For iRow = 1 To SourceRange.Rows.Count
kDate = SourceRange.Cells(iRow, 1).Value
If kDate <> "" And IsDate(kDate) Then
If Dict.Exists(kDate) Then
Dict(kDate) = Dict(kDate) + SourceRange.Cells(iRow, 2).Value
Else
Dict.Add kDate, SourceRange.Cells(iRow, 2).Value
End If
End If
Next
' Write header
OutputRange.Cells(1, 1).Value = "Date"
OutputRange.Cells(1, 2).Value = "Sum"
' Write result to output range
iRow = 2
For Each kDate In Dict.Keys
OutputRange.Cells(iRow, 1).Value = kDate
OutputRange.Cells(iRow, 2).Value = Dict(kDate)
iRow = iRow + 1
Next
End Sub2. Click the 
button or press F5 to run the macro.
3. A dialog box will prompt you to select your original data range (ensure the date is in the first column and the value in the second). Then, another dialog allows you to specify where to output the results (e.g., cell E1).
4. After confirming, a summary table of dates and the sum of their corresponding values will be generated starting from your chosen output cell.
Tips and error reminders:
- This macro automatically handles duplicate dates regardless of their position in the list.
- If you select an output position that would overwrite original data, the macro will not prevent overwriting—be sure to choose a blank area.
- The macro skips blank or non-date values in the date column; ensure your data is clean for best results.
- This method is optimal for automating repeated summarizing processes or integrating into batch data manipulation tasks.
VBA works best when you need automated batch processing or wish to avoid manual intervention. It is an excellent choice for large-scale or regularly recurring data merges. However, if you only need occasional summarization, the formula or Pivot Table methods may be simpler.
Sum corresponding values based on same date with Pivot Table
A Pivot Table offers a convenient, formula-free way to group data by date and calculate sums in just a few clicks. It is especially suited for large datasets or when you want to explore and analyze data interactively. Pivot Tables can be refreshed automatically when your data changes, making them suitable for ongoing reports and dashboards.
1. Select your original data range, including headers (e.g., A1:B13).
2. Go to the Insert tab and select PivotTable. In the dialog box, confirm your data range and choose where you wish to place the Pivot Table (new worksheet or existing worksheet).
3. In the Pivot Table Field List, drag the Date field into the Rows area, and the corresponding Order (or your values field) into the Values area. Excel will automatically sum the values for each unique date.
4. To update Pivot Table results when your original data changes, right-click on the Pivot Table and select Refresh.
Advantages: The Pivot Table method does not require functions or code, and supports interactive filtering, sorting, and grouping for deeper data analysis. It is scalable and reliable for both small and large datasets.
Drawbacks: Less flexible for integrating calculations directly into cell formulas, and some users may need time to get familiar with the Pivot Table interface.
Troubleshooting and practical suggestions:
- Make sure your data range includes headers and consistent data types.
- If the Pivot Table does not display unique dates as expected, check for hidden formatting or use "ungroup".
- You can add multiple calculation fields or group by month/quarter/year for broader summaries.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in