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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel sum by day


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.

Dataset to sum by day in Excel


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.

Using SUMPRODUCT & DAY functions to sum by day

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.

Result found after using the SUMPRODUCT & DAY functions

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.

Read More: Sum Formula Shortcuts in Excel


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.

Combining SUM, IF & DAY functions to sum by day in Excel

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.

Dataset to apply 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.

Applying SUMIFS function to sum by day

  • Thus, you can sum by day applying the SUMIFS function.

Results found after using 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.

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.

Dataset to use the SUMIFS and DATE functions

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.

Using SUMIFS and DATE functions to sum by day in Excel

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.

Dataset to use the SUMPRODUCT and WEEKDAY functions

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.

Using SUMPRODUCT and WEEKDAY functions to sum by day in Excel

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.

Results found after using the SUMPRODUCT & WEEKDAY function


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.

Using SUMPRODUCT and TEXT functions to sum by day

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

Results found after using the SUMPRODUCT & TEXT functions

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.

Inserting Pivot Table

  • 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.

PivotTable from table or range box

  • 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.

Dragging fields between Rows and Values area

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

Selecting Group command

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

Grouping box

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

Results found after inserting 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.

Opening Microsoft Visual Basic operator

  • Then, expand Insert >> select Module.

Inserting Module in Microsoft Visual Basic Editor

  • 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

Inserting Code into the module

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.

Saving code

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

Opening Macros box

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

Macro box

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

Sum by day using VBA in Excel


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.

Practice Section


Download Practice Workbook

You can download the workbook to practice yourself.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo