Home » How to Calculate Sum by Date in Excel

How to Calculate Sum by Date in Excel

by Tutor Aspire

You can use the following formula to calculate the sum of values by date in an Excel spreadsheet:

=SUMIF(A1:A10, C1, B1:B10)

This particular formula calculates the sum of values in the cell range B1:B10 only where the corresponding cells in the range A1:A10 are equal to the date in cell C1.

The following example shows how to use this formula in practice.

Example: Calculate Sum by Date in Excel

Suppose we have the following dataset that shows the sales of some product on various dates:

Now suppose we’d like to calculate the sum of sales by date.

To generate a list of unique dates, we can use the following formula:

=SORT(UNIQUE(A2:A15))

We’ll type this formula into cell D2:

By default, Excel converts the date to the number of days since 1/1/1900.

To convert these numbers to a recognizable date format, simply highlight the cell range D2:D6, then click the format dropdown arrow and click Short Date:

The dates will be converted to a recognizable date format:

Next, we can use the following formula to calculate the sum of sales by date:

=SUMIF($A$2:$A$15, D2, $B$2:$B$15)

We’ll type this formula into cell E2, then copy and paste it into each remaining cell in column E:

Excel sum by date

From the output we can see:

  • The sum of sales on 1/4/2022 is 15.
  • The sum of sales on 1/7/2022 is 19.
  • The sum of sales on 2/7/2022 is 19.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count by Month in Excel
How to Count by Year in Excel
How to Calculate a Cumulative Sum by Date in Excel

You may also like