How to Sum Values by Day in Excel (6 Methods)

You can make summations in Excel based on a specific date or day. In this article, I’ll show you six methods 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 of a particular day of every month. We will also make the summation of sales on different weekdays.

dataset

Download Practice Workbook

6 Methods to Sum Values by Day

1. Using SUMPRODUCT Function

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. Type the following formula in an empty cell (E7),

=SUMPRODUCT((DAY(A6:A18)=B3)*C6:C18)

Here, the DAY function will return the day of every month from the dates in cells A6:A18. B3 is the criteria which is the day no 15. The SUMPRODUCT function will make the summation of the values from cells C6:C18 which match the criteria.

SUMPRODUCT AND DAY

Press ENTER and you will get the summation of total sales on day number 15 of different months.

SUM BY DAY IN EXCEL

Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)

2. SUM IF DAY Functions to Sum Values by Day

In the second method, we will sum values by day using the SUM function, the IF function, and the DAY function. Type the following array formula in an empty cell (E7) to get the sum of day number 15 of every month,

=SUM(IF(DAY(A6:A18)=B3,C6:C18,0),0)

Here, the DAY function will return the day of every month from the dates in cells A6:A18. B3 is the criteria which is day no 15. The IF function will match the criteria with the values of cells C6:C18 and the SUM function will make the summation of the values which match the criteria.

SUM, IF, DAY

To get the value from the array formula, press CTRL+SHIFT+ENTER.

SUM BY DAY IN EXCEL

3. Sum Values by Day of Week by SUMIFS Function

In the third method, we will sum values based on different days of a week by using the SUMIFS function. Type the following formula in cell F6,

=SUMIFS($C$6:$C$18,$B$6:$B$18,E6)

Here, $C$6:$C$18 is the sum range, $B$6:$B$18 is the criteria range and E6 is the criteria. The SUMIF function will make sum of those values in which data from the criteria range match with the given criteria.

SUMIFS

Press ENTER and you’ll get the sum of sales of Sunday.

SUMIFS

Drag the F7 cell and you will get the summations for all of the weekdays.

SUM BY DAY IN EXCEL


Similar Readings


4. SUMPRODUCT Function to Sum Values by Day of Week

In the fourth method, we will use the SUMPRODUCT function and the WEEKDAY function to sum values of different days of a week. To use this function first we need to define the days of a week. We have to give 1 for Sunday as the day of the week number, 2 for Saturday and so on.

Now type the following formula in cell G7,

=SUMPRODUCT(--(WEEKDAY($A$6:$A$18)=E6),$C$6:$C$18)

Here the WEEKDAY function will return the day number of the dates in cells $A$6:$A$18 which will be matched with the criteria. The SUMPRODUCT function will add up the values from cells

$C$6:$C$18 which day numbers match with the criteria.

SUMPRODUCT

Press ENTER to get the total sales of Sunday.

SUMPRODUCT

Drag cell G7 to apply the formula in other cells. As a result, you will get the sum for all of the days of a week.

SUM BY DAY IN EXCEL

5. SUM Data by Days with Pivot Table

We will use the Pivot table to sum data by days as the fifth method. First, you have to create a pivot table with your dataset. Select your dataset, Go to the Insert tab, and select PivotTable.

PIVOT TABLE

Now, a dialog box named PivotTable from table/range will appear. Check if the Table/range matches with your dataset. After that, 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, you can select New Worksheet. Finally, press OK.

PIVOT TABLE

After that, the pivot table will be created and you can see a right panel in your Excel named as PivotTable Fields.

PIVOT TABLE

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.

PIVOT TABLE FIELDS

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

SUM BY DAY IN EXCEL

A box named Grouping will appear. From this box select Days and click on OK.

GROUP

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

SUM BY DAY IN EXCEL

6. Using VBA

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

First, open the VBA window by pressing ALT+F11. After that right click on the sheet name from the left panel of the VBA window, expand Insert, and select Module.

VBA WINDOW

It will open a Module(Code) window. Insert the following code in this window,

Sub Sum_values_by_day()

Dim ws As Worksheet

Set ws = Worksheets("VBA")

Set sday = ws.Range("B3")

For i = 6 To 18

If Day(ws.Cells(i, 1)) = sday Then

sumday = sumday + ws.Cells(i, 3).Value

End If

Next i

ws.Range("E7") = sumday

End Sub

Here the VBA code will make the summation of the values of sale after matching with the criteria which is given in cell B3. Finally, it will return the sum up value in cell E7.

VBA CODE

After inserting the code, close the VBA window and open Macro from the View tab.

MACRO

In the Macro window select the macro and click on Run.

MACRO WINDOW

As a result, you will get the sum by day in cell E7.

SUM BY DAY

Conclusion

I have described six 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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo