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)

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:

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 Sub

2. 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.

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.