Counting the occurrences per day is a pretty common task while working in Excel. In this article, you’ll learn some useful methods to count occurrences of a happening per day.

**Table of Contents**hide

## **Download Practice Book**

You can download the free practice Excel template from here and practice on your own.

**4 Quick Methods to Count Occurrences Per Day in Excel**

**Method 1: Use COUNTIF Function to Count Occurrences Per Day in Excel**

Let’s have a look at our dataset first. Here I have used some random order dates for several products. You’ll notice that some dates are common. So now we’ll use **the COUNTIF function** to count the occurrence per day. This function is used to count the number of cells that meet a criterion.

Here I’ll find the occurrence for the day **“10-Oct”. **That’s why I have placed it in** Cell E6.**

**Steps:**

➧ Activate **Cell F6**

➧ Type the Given formula-

`=COUNTIF(C5:C12,E6)`

➧ Then hit the **Enter **button to get the result.

**Read More:** **Count Number of Occurrences of Each Value in a Column in Excel**

**Method 2: Insert SUMPRODUCT Function in Excel to Count Occurrences Per Day**

In this method, I’ll show how to use **the SUMPRODUCT function** for counting the occurrences per day. **SUMPRODUCT **function is used to return the sum of the products of corresponding ranges or arrays.

**Steps:**

➧ Type the formula in **Cell F6** which is given below:

`=SUMPRODUCT(--(C5:C12=E6))`

➧ After that, just press the **Enter **button.

See that we have found our occurrences for the day **“10-Oct”**

**🔽**** Breakdown of the Formula:**

**➥**** (C5:C12=E6)**

Here it will check the value of **Cell E6** to the array **C5:C12** whether it matches or not. So the result will return as-

**{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}**

**➥**** –(C5:C12=E6)**

The “**—**” sign before the formula will convert the previous result to binary format. **0** for **False **and **1** for **True**. It will return as-

**{0;1;0;0;0;1;0;1}**

**➥**** SUMPRODUCT(–(C5:C12=E6))**

Finally, the** SUMPRODUCT** function will sum up the values and will return as-

**{3}**

**Read More:** **Excel VBA to Count Duplicates in a Column (A Complete Analysis)**

**Method 3: Create Pivot Table to Count Occurrences Per Day in Excel**

Pivot Table is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. Now I will show how to use Pivot Table to count occurrences per day.

**Step 1:**

➧ Select any cell from the dataset.

➧ Then click **Insert > Pivot Table**.

A **Pivot Table** dialog box will open up.

**Step 2:**

➧ Select **Existing Worksheet**.

➧ Choose the cell from the **Location **bar where you want to create **Pivot Table**. I have selected **E4**.

➧ Then press **OK**

A** “Pivot Table Field” **box will appear at the right side of your sheet.

**Step 3:**

➧ Mark only **Order Date.**

➧ Then drag it to the **Rows **and **Values** fields.

The **Pivot Table** will then show the occurrences for every unique day

**Read More:** **Count Duplicates in Excel Pivot Table (2 Easy Ways)**

**Similar Readings**

**How to Ignore Blanks and Count Duplicates in Excel (3 Ways)****Count Duplicate Values in Multiple Columns in Excel (6 Ways)****Count Duplicates in Two Columns in Excel (8 Methods)****How to Count Duplicate Rows in Excel (4 Methods)**

**Method 4: Embed Excel VBA to Count Occurrences Per Day**

In our last method, I’ll use Excel **VBA **to do the operation. Here I’ll find the occurrences for the day “**11-May”** in **Cell F6**

**Step 1:**

➧ **Right-click** your mouse to the sheet name.

➧ Select the **View Code** option from the **context menu**.

A **VBA **window will open up.

**Step 2:**

➧ Write the codes given below:

```
Option Explicit
Sub Count_by_Day()
Dim ws As Worksheet
Dim output As Range
Dim dayvalue As String
Dim counter As Integer
Dim y As Integer
Set ws = Worksheets("Sheet")
Set output = ws.Range("F6")
dayvalue = ws.Range("E6")
counter = 0
For y = 5 To 12
If (ws.Range("C" & y)) = dayvalue Then
counter = counter + 1
End If
Next y
output = counter
End Sub
```

➧ Press the **Play **button to run the codes.

A dialog box named “**Macro” **will appear.

**Step 3:**

➧ Press **Run **option.

See that we have found the occurrences for that day.

**Read More:** **VBA to Count Duplicates in Range in Excel (4 Methods)**

**Conclusion**

I hope all of the methods described above will be well enough to count occurrences per day. Feel free to ask any questions in the comment section and please give me feedback.