Prantick Bala
You can make summations in Excel based on a specific date or day. In this article, we’ll show you 8 easy ways to sum values by day in Excel.
Download Practice Workbook
You can download the workbook to practice yourself.
8 Easy Ways to Sum Values by Day in Excel
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.
Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)
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.
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.
Similar Readings
- All the Easy Ways to Add up (Sum) a column in Excel
- How to Sum Between Two Numbers Formula in Excel
- How to Sum Colored Cells in Excel (4 Ways)
- SUM Ignore N/A in Excel( 7 Easiest Ways)
- How to Use VLOOKUP with SUM Function in Excel (6 Methods)
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.
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.
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.
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.
Related Articles
- How to Sum Multiple Rows and Columns in Excel
- Sum Cells in Excel: Continuous, Random, With Criteria, etc.
- How to Add Percentages to Numbers in Excel (4 Easy Ways)
- How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)
- 3 Easy Ways to Sum Top n Values in Excel
- Excel Sum Last 5 Values in Row (Formula + VBA Code)
