# How to Count Occurrences Per Day in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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

For example, by using the COUNTIF function, we got the occurrences in cell F5 of the date mentioned in cell E5. If we need to get the occurrences for other dates, we just have to mention the date in cell E5. ## 4 Quick Methods to Count Occurrences Per Day in Excel

Here, we can use the following dataset in order to get the occurrences. For this, we got the product name and the Order date column. In order to avoid any compatibility issues, try to use the Excel 365 edition. ### 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.

Steps:

• Activate Cell F6.
• Then, type the given formula-
`=COUNTIF(C5:C12,E6)`
• Then hit the Enter button to get the result. ### Method 2: Insert SUMPRODUCT Function in Excel to Get 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:

• First, type the formula in Cell F6 which is given below:
`=SUMPRODUCT(--(C5:C12=E6))`
• After that, just press the Enter button.
• Then 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}

### Method 3: Create Excel Pivot Table to Count Daily Occurrences

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

Steps:

• Select any cell from the dataset.
• Then click Insert > Pivot Table.
• Hence a Pivot Table dialog box will open up. • Select Existing Worksheet.
• After that 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 on the right side of your sheet. • Mark only Order Date.
• Then drag it to the Rows and Values fields.
• Lastly, the Pivot Table will then show the occurrences for every unique day. ### 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.

Steps:

• Firstly Right-click your mouse on the sheet name.
• Select the View Code option from the context menu.
• Hence, a VBA window will open up. • 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``````
• Then press the Play button to run the codes. • After that, a dialog box named “Macro” will appear.
• Then press the Run option. • Finally, we have found the occurrences for that day. ## 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.

## Related Articles #### Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  