Md. Sourov Hossain Mithun
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.
Download Practice Workbook
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
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.
Read More: Count Number of Occurrences of Each Value in a 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”
🔽 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 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.
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.
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.
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.
