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.

Using the COUNTIF function we get the occurrence of the E5 cell date in the range of cell C5:C13.


How to Count Occurrences Per Day in Excel: 4 Quick Ways

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.

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

Steps:

  • Activate Cell F6.
  • Then, type the given formula-
=COUNTIF(C5:C12,E6)
  • Then hit the Enter button to get the result.

Use COUNTIF Function to Count Occurrences Per Day in Excel

Read More: How to Count Duplicates in Column in Excel


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”

Insert 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}

Read More: How to Count Duplicates in Two Columns in Excel


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.

Create Pivot Table to Count Occurrences Per Day in Excel

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

Specify your pivot table range

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

Drag the Pivot table fields to various sections

Read More: How to Count Duplicate Values in Multiple Columns in Excel


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.

Embed Excel VBA to Count Occurrences Per Day in Excel

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

Custom Code entered in the VBA code editor

  • After that, a dialog box named “Macro” will appear.
  • Then press the Run option.

Run the appropriate macro from the Macros dialog box

  • Finally, we have found the occurrences for that day.

Executing of the VBA code to count occurrences per day in excel

Read More: How to Count Duplicate Rows in Excel


Download Practice Workbook

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


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


<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo