How to Sum Values by Day in Excel (8 Easy Ways)

In this article, we’ll demonstrate 8 easy ways to sum values by day in Excel. Here is an overview:

8 Easy Ways to Sum Values by Day in Excel

Suppose we have a dataset of Sales on different dates. We’ll find the sum of sales on a particular day of every month, and also the sum of sales on different weekdays.

Method 1 – Using the SUMPRODUCT & DAY Functions to Sum Values by Day in Excel

In the first method, we will use the SUMPRODUCT function and the DAY function to sum values by day. Suppose we want to find the sum of sales on day number 15 of every month.

Steps:

Select Cell C17 and insert the following formula:

=SUMPRODUCT((DAY(B5:B14)=C16)*D5:D14)

Press ENTER to return the sum of total sales on day number 15 of different months.

Here, the DAY function will return the day of every month from the dates in cells B5:B14. C16 is the criteria, which is day no 15. The SUMPRODUCT function will find the sum of the values from cells D5:D14 which match the criteria.

Read More: Sum Formula Shortcuts in Excel

Method 2 – Combining the SUM, IF & DAY Functions to Calculate Total Value

Steps:

Enter the following array formula in an empty cell (C17) to get the sum for day number 15 of every month:

=SUM(IF(DAY(B5:B14)=C16,D5:D14,0),0)

Press Enter if you are using Excel 365, or Ctrl + Shift + Enter for other Excel versions.

Here, the DAY function will return the day of every month from the dates in cells B5:B14. C16 is the criteria, which is day no 15. The IF function will match the criteria with the values of cells D5:D14, and the SUM function will find the sum of the values which match the criteria.

Method 3 – Using the SUMIFS Function to Sum Values by Day of the Week in Excel

Next, we will sum values based on different days of the week by using the SUMIFS function.

Steps:

Select Cell G5 and insert the following formula:

=SUMIFS($D$5:$D$14,$C$5:$C$14,F5)

Press ENTER to return the sum of sales for Sunday.
Drag Cell G5 to get the sum for all of the weekdays.

And that is how to sum by day with the SUMIFS function.

Here, $D$5:$D$14 is the sum range, $C$5:$C$14 is the criteria range and F5 is the criteria. The SUMIF function will make a sum of those values in which data from the criteria range match the given criteria.

Method 4 – Using the SUMIFS Function with the DATE Function to Calculate Total Value by Day

Suppose we want to calculate the Total Sales from February and on the 15 of every month. We can do this by using the SUMIFS and DATE Functions together.

Steps:

Enter the following array formula in an empty cell (C16) to get the sum for day number 15 of every month from February.

=SUMIFS(D5:D14,B5:B14,">="&DATE(2021,2,1),C5:C14,"Monday")

Press Enter if you are using Excel 365, or Ctrl + Shift + Enter for other Excel versions.

In the formula, the DATE function is used to return 1st February 2021. Then the SUMIFS function will sum the values of cell range D5:D14, if the cell range B5:B14 is greater than or equal to 1st February 2021 and cell range C5:C14 is Monday.

Method 5 – Summing Values by Day of the Week Using the SUMPRODUCT & WEEKDAY Functions

Now, we will use the SUMPRODUCT and WEEKDAY functions to sum values of different days of a week. To use this function first we need to define the days of the week. We’ll give 1 for Sunday, 2 for Monday, and so on.

Steps:

Enter the following formula in Cell H5:

=SUMPRODUCT(--(WEEKDAY($B$5:$B$14)=F5),$D$5:$D$14)

Press ENTER to return the total sales for Sunday.
Drag Cell H5 to apply the formula in the other cells.

As a result, we have the sum for all of the days of the week.

Here, the WEEKDAY function will return the day number of the dates in cells $B$5:$B$14 which will be matched with the criteria. The SUMPRODUCT function will add up the values from the matched cells.

Method 6 – Using the SUMPRODUCT Function with the TEXT Function to Sum Data by Day

We can also use the SUMPRODUCT function with the TEXT function to sum data of different days of a week.

Steps:

Select Cell G5 and insert the following formula:

=SUMPRODUCT(--(TEXT($C$5:$C$14,"ddd")=F5),$D$5:$D$14)

Press ENTER to return the total sales for Sunday.
Drag down the Fill Handle tool to copy the formula to the rest of the cells.

We have the Total Sales value for different days of a week.

To start with, the TEXT function will change the date format in cells $C$5:$C$14 which will then be matched with the criteria. Then, the SUMPRODUCT function will add up the values from the matched cells.

Method 7 – Inserting a Pivot Table to Sum Data by Day in Excel

We can also insert a Pivot Table to sum data by day in Excel.

Steps:

Select the dataset.
Go to the Insert tab >> select PivotTable.

A dialog box named PivotTable from table/range will appear.

Check if the Table/range matches your dataset and adjust if not.
Select Existing worksheet and select an empty sheet from your worksheet as Location.
If you want to create the Pivot table in a new worksheet, select New Worksheet.
Click OK.

The pivot table will be created, and a right panel will open in the worksheet named PivotTable Fields.

From PivotTable Fields, drag Sales Date into Rows and Sales Amount into Values.

A row named Months will be automatically added in Rows, and the sales in the different months will be displayed in the pivot table.

Right-click on any cell in the first column of the PivotTable and select Group.

A box named Grouping will appear.

From this box select Days and click on OK.

Thus, we will get the sum by day in our pivot table.

Method 8 – Using VBA to Calculate the Total Value by Day

In our final method, we’ll use Visual Basic Applications (VBA) to sum values by day.

Steps:

Go to the Developer tab >> click on Visual Basic. Or press ALT + F11.

Select Insert >> Module.

Insert the following code in the Module window that opens:

Sub Sum_values_by_day()
Dim worksheet1 As Worksheet
Set worksheet1 = Worksheets("VBA")
Set day1 = worksheet1.Range("C16")
For row_no = 5 To 15
If Day(worksheet1.Cells(row_no, 2)) = day1 Then
sum_by_day = sum_by_day + worksheet1.Cells(row_no, 4).Value
End If
Next row_no
worksheet1.Range("C17") = sum_by_day
End Sub

Code Breakdown

To start with, we declare the Sub procedure named Sum_values_by_day.
Then, we set worksheet1 as worksheet name VBA, and day1 as Cell C16 to check for this criteria.
We then use a For Next loop from rows 5 to 15 to sum the values of sales that match with the criteria in Cell C16.
Finally, the sum is returned in Cell C17.