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

To demonstrate our methods, we’ll use the following dataset, containing columns for Product Name and Order Date. We used Excel 365 here, but the same methods should apply in other versions of Excel too.

Sample Dataset to Count Occurrences Per Day in Excel

Method 1 – Use COUNTIF Function

In our dataset, some dates are common to multiple products. Let’s use the COUNTIF function, which is used to count the number of cells that meet a criterion, to count the occurrences per day.


  • Activate Cell F6.
  • Enter the following formula:
  • Press Enter button to return the result.

Use COUNTIF Function to Count Occurrences Per Day in Excel

Read More: How to Count Duplicates in Column in Excel

Method 2 – Use SUMPRODUCT Function

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.


  • Enter the following formula in Cell F6:
  • Press Enter.

The occurrences for the day “10-Oct” are returned.

Insert SUMPRODUCT Function in Excel to Count Occurrences Per Day

Breakdown of the Formula:

  • (C5:C12=E6)

We check the value of Cell E6 against the array C5:C12 to see whether it matches or not. The result:


  • –(C5:C12=E6)

The “” sign before the formula will convert the previous result to binary format (0 for False and 1 for True). The result:


  • SUMPRODUCT(–(C5:C12=E6))

The SUMPRODUCT function will sum the values. The result:


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

Method 3 – Use an Excel Pivot Table

Pivot Table is a tool that can calculate, summarize, and analyze data. It lets you see comparisons, patterns, and trends in the data.


  • Select any cell from the dataset.
  • Click Insert > Pivot Table.

Create Pivot Table to Count Occurrences Per Day in Excel

A Pivot Table dialog box will open up.

  • Select Existing Worksheet.
  • Select the cell from the Location bar where you want to create the Pivot Table. Here, we select cell E4.
  • Click OK.

Specify your pivot table range

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

  • Check only Order Date.
  • Drag it to the Rows and Values fields.

The Pivot Table displays 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 – Use Excel VBA

Let’s find the occurrences for the day “11-May” in Cell F6 using VBA code.


  • Right-click your mouse on the sheet name.
  • Select the View Code option from the context menu.

Embed Excel VBA to Count Occurrences Per Day in Excel

A VBA window will open up.

  • Enter the following code:
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.

Custom Code entered in the VBA code editor

A dialog box named “Macro” will appear.

  • Click Run.

Run the appropriate macro from the Macros dialog box

The occurrences for that day are returned.

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

Read More: How to Count Duplicate Rows in Excel

Download Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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