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.

**Table of Contents**hide

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

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

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

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

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

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

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

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

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