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

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.


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.

COUNTIF Function to Count Occurrences Per Day in Excel

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: Excel Count Number of Occurrences of Each Value in a Column


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”

SUMPRODUCT Function in Excel to Count Occurrences Per Day

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

Pivot Table to Count Occurrences Per Day in Excel

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.

Pivot Table to Count Occurrences Per Day in Excel

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


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.

Excel VBA to Count Occurrences Per Day

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.

Excel VBA to Count Occurrences Per Day

A dialog box named “Macro” will appear.

Step 3:

➧ Press Run option.

Excel VBA to Count Occurrences Per Day

See that 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

Mithun

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

Leave a reply

ExcelDemy
Logo