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.

**Table of Contents**hide

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

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

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

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

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

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

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

Press **ENTER **to get the total sales of Sunday.

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.

### 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**.

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

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

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

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

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

### 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**.

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

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

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

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

## 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**

**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)**