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

Let’s say we have a dataset of Sales on different dates. Now we will find the summation of sales on a particular day of every month. We will also make the summation of sales on different weekdays.

1. Use 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 make a summation of sales of day number 15 of every month.

To do that, follow the steps shown below.

Steps:

Firstly, select Cell C17 and insert the following formula.

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

Then, press ENTER and you will get the summation 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 the day no 15. The SUMPRODUCT function will make the summation of the values from cells D5:D14 which match the criteria.

2. Combine SUM, IF & DAY Functions to Calculate Total Value

In the second method, we will sum values by day using the SUM, IF, and DAY functions.

Steps:

In the beginning, type the following array formula in an empty cell (C17) to get the sum of day number 15 of every month.

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

After that, press Enter if you are using Excel 365, or else press 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 make the summation of the values which match the criteria.

3. Apply SUMIFS Function to Sum Values by Day of Week in Excel

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

Steps:

Firstly, select Cell G5 and insert the following formula.

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

Now, press ENTER and you’ll get the sum of sales of Sunday.
Then, drag Cell G5 and you will get the summations for all of the weekdays.

Thus, you can sum by day applying 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.

Similar Readings

4. Use Excel SUMIFS with DATE Function to Calculate Total Value by Day

Suppose you want to calculate Total Sales from February and on the 15 of every month. You can do it by using the SUMIFS and DATE Functions in Excel.

Steps:

To start with, type the following array formula in an empty cell (C16) to get the sum of day number 15 of every month from February.

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

After that, press Enter.

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.

5. Sum Values by Day of Week Using SUMPRODUCT & WEEKDAY Functions

In the fifth method, 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 have to give 1 for Sunday as the day of the week number, 2 for Monday, and so on.

Steps:

Firstly, type the following formula in Cell H5.

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

After that, press ENTER to get the total sales of Sunday.
Lastly, drag Cell H5 to apply the formula in other cells.
As a result, you will get the sum for all of the days of a 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 cells.

6. Utilize SUMPRODUCT Function with TEXT Function to Sum Data by Day

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

Steps:

In the beginning, select Cell G5 and insert the following formula.

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

Then, press ENTER to get the total sales of Sunday.
Lastly, drag down the Fill Handle tool to copy the formula to the rest of the cells.

Thus, you will get the Total Sales value of different days of a week.

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

7. Insert Pivot Table to Sum Data by Day in Excel

Now, we will insert a Pivot Table to sum data by day in Excel.

Here are the steps to do that.

Steps:

Firstly, select your dataset.
Then, go to the Insert tab >> select PivotTable.

Now, a dialog box named PivotTable from table/range will appear. Check if the Table/range matches your dataset.
After that, select Existing worksheet and select an empty sheet from your worksheet as Location.
However, if you want to create the Pivot table in a new worksheet, you can select New Worksheet.
Finally, press OK.

Then, the pivot table will be created and you can see a right panel in your Excel named PivotTable Fields.
Next, from PivotTable Fields drag Sales Date in Rows and Sales Amount in Values.
A row named Months will be automatically added in Rows and you will see the sales in the different months in the pivot table.

Now, right-click on any cell of the first column of the PivotTable and select Group.

After that, a box named Grouping will appear. From this box select Days and click on OK.

Thus, you will get the sum by day in your pivot table.

8. Use VBA to Calculate Total Value by Day

In the final method of this article, we’ll show you how you can use Visual Basic Applications (VBA) to sum values by day.

Steps:

In the beginning, go to the Developer tab >> click on Visual Basic.

Then, expand Insert >> select Module.

After that, insert the following code in this window.

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 declared the Sub procedure named Sum_values_by_day.
Then, we set worksheet1 as worksheet name VBA day1 as Cell C16 to check for this criteria.
After that, we used a For Next loop from rows 5 to 15 to sum the values of sales after matching with the criteria which is given in Cell C16.
Finally, it will return the sum-up value in Cell C17.

Next, Save the code and close the VBA window.

Now, go to the Developer tab >> click on Macros.

Then, in the Macro window select the macro and click on Run.

As a result, you will get the sum by day in Cell C7.

Practice Section

We’ve given a practice sheet in the workbook to practice these explained ways to sum values by day in Excel. You can download it from the above.

Conclusion

I have described 8 methods to sum values by day in Excel in this Article. You can use any of the methods to sum values based on day or weekday. If you face any confusion regarding any of the methods, please leave a comment.

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

Download Practice Workbook

8 Easy Ways to Sum Values by Day in Excel

1. Use SUMPRODUCT & DAY Functions to Sum Values by Day in Excel

2. Combine SUM, IF & DAY Functions to Calculate Total Value

3. Apply SUMIFS Function to Sum Values by Day of Week in Excel

4. Use Excel SUMIFS with DATE Function to Calculate Total Value by Day

5. Sum Values by Day of Week Using SUMPRODUCT & WEEKDAY Functions

6. Utilize SUMPRODUCT Function with TEXT Function to Sum Data by Day

7. Insert Pivot Table to Sum Data by Day in Excel

8. Use VBA to Calculate Total Value by Day

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply