Tanzim
There may be times when you need to know easily how to count the occurrences easily per day in Excel. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to count the occurrences easily per day in Excel with various methods and lots of examples.
📁 Download Excel File
To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.
Learn to Count Occurrences Per Day in Excel Using These 4 Easy Ways
Let’s start by looking at our data set. I’ve used some very random joining dates for a few employees here. You’ll see that some of the dates are the same. Now we will be counting occurrences per day in Excel with these 4 easy methods.
1. Utilising COUNTIF Function
Here we will utilize COUNTIF function to get the daily frequency of occurrences. The total number of used cells fulfilingl a certain requirement can be determined with the help of this function. This formula uses COUNTIF function to find each name of one Column in another Column. For each piece of data in the first Column, the function will give back a number that shows how many times it found a match.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell F6. Then we put the formula below:
- Later we press Enter. The final result will be like this.
Read More: 3 Ways to Count Duplicate Values in Excel Only Once
2. Inserting SUMPRODUCT Function
For counting occurrences easily per day using Excel, we can now utilize SUMPRODUCT function. SUMPRODUCT function counts and adds up all the ones and zeros in an array.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell F6. Then we put the formula below:
- Then we press The final result will be like this.
Read More: 7 Ways to Count Duplicates in Two Columns in Excel
3. Making Pivot Table
The Pivot Table feature is one of the very useful tools for calculating, summarizing, and analyzing data that helps us see comparisons, various patterns, and some trends in the data. Now I’ll demonstrate how to count the number of occurrences that occur each day by using the Pivot Table.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select a cell, then go to Insert next to the Home tab and select Pivot Table. Then we select Table/Range from the drop down box.
- From the new dialogue box, first, we select the whole range. Then we choose Existing Worksheet, then we select cell E6 as the location of the pivot table. Finally, we press OK.
- On the right side of our sheet, a box called “Pivot Table Field” will appear.
- Here we select Order Date and drag it in the two boxes shown below.
- Here is the final result.
4. Implementing Excel VBA
Now we will use VBA. Before talking about the method, first, we have enabled Developer Tab on our Ribbons. Let’s follow the steps below:
⬇️⬇️ STEPS ⬇️⬇️
- Let’s press Alt+F11 to bring up the VBA You can also do this by going to the Developer tab and clicking on Visual Basic.
- Then we choose Module from the Insert This will bring up the window for the VBA Module. Here is where we will put our code.
- Now we type the code below:
Sub Count_by_Day()
Dim wsht As Worksheet
Dim ResCell As Range
Dim DatVal As String
Dim LiveCount As Integer
Dim p1 As Integer
Set wsht = Worksheets("VBA")
Set ResCell = wsht.Range("F6")
DatVal = wsht.Range("E6")
LiveCount = 0
For p1 = 6 To 13
If (wsht.Range("C" & p1)) = DatVal Then
LiveCount = LiveCount + 1
End If
Next p1
ResCell = LiveCount
End Sub- Now we save and run the code.
- Now we will see the result.
📄 Important Notes
🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.
🖊️ A practice workbook is given so that you can practice yourself.
🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.
🖊️ At the side of each Excel worksheet file, there is space where they can practice.
📝 Takeaways from This Article
📌 Reader will be able to calculate production per hour in excel in various methods.
📌 They can use COUNTIF function for counting occurrences easily per day using Excel.
📌 Viewers can also insert SUMPRODUCT function in Excel for counting occurrences per day.
📌 Readers can utilize Excel VBA to track the number of events per day.
📌 Finally, they can create a pivot table to count the occurrences per day in Excel.
Conclusion
That concludes today’s session. These are the methods for using Excel to count the occurrences per day in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and inquiries in the comments section and explore our website, ExcelDen, the best Excel solutions provider.
