Table of Contents
To calculate the sum of data by date in Excel, you can use the SUMIFS and SUMPRODUCT functions. The SUMIFS function allows you to sum data based on multiple criteria, including date, and the SUMPRODUCT function allows you to sum data based on more than one column. Both functions require you to specify the range of cells to be summed and the criteria for summing. You can then use the date criteria to calculate the sum for any desired period.
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:
=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:
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.