Method 1 – Number of Occurrences of a Particular Date
1.1 Using the COUNTIF Function
Steps:
- Select cell E5.
- Enter the following formula:
=COUNTIF(C5:C243,"18-Aug-1888")
- Press Enter.
This is how we can easily count occurrences of a particular date in Excel using the COUNTIF function.
1.2 Using the SUMPRODUCT Function
Steps:
- Select cell E5.
- Enter the following formula:
=SUMPRODUCT(--(C5:C25="18-Aug-1888"))
Breakdown of the Formula
Here, C4:C23=”18-Aug-1888″ compares every cell in the range C4 to C23 and returns a TRUE if the date is Aus 18, 1888. Otherwise, it returns FALSE.
The (–) portion converts the array of Boolean values (TRUE and FALSE) into an array of 1 and 0, 1 for TRUE, and 0 for FALSE.
The SUMPRODUCT() function then returns the sum of these 1s and 0s. This is the total number of cells with the date August 18, 1888.
- Press Enter.
Owing to the formula, this function will return the total date occurrence of that particular input from the Excel spreadsheet.
1.3 Using a Pivot Table
Steps:
- Select any cell from the dataset.
- Go to the Insert tab on your ribbon.
- Select PivotTable from the Tables group section.
- The dataset range and where you want to put the pivot table are in the next box. We have chosen a new worksheet to demonstrate.
- Click on OK. A new spreadsheet will open.
- Go to the PivotTable Fields.
- Click and drag the Date of Publication to both Rows and Values fields individually.
- The pivot table will appear in the desired place.
Method 2 – Total Number of Dates in a Particular Range
2.1 Using the COUNTIFS Function
Steps:
- Select cell E5.
- Enter the following formula:
=COUNTIFS(C5:C17,">1/1/1940",C5:C17,"<=12/31/1950")
- Press Enter.
There are three books covering the years 1940 to 1950. This is how we can count date occurrences from a particular range in Excel.
2.2 Using the eSUMPRODUCT Function
Steps:
- Select cell E5.
- Enter the following formula in the cell:
=SUMPRODUCT(((C5:C17)>=DATE(1940,1,1))*((C5:C17)<=DATE(1950,12,31)))
- Press Enter.
This is how we can use the function to count date occurrences from a particular range in Excel.
2.3 Using VBA Code
Steps:
- Go to the Developer tab on your ribbon.
- Select Visual Basic from the Code group section.
- The VBA window will open up.
- Select the Insert tab in it.
- Select Module from the drop-down menu.
- If the module isn’t already selected, select it.
- Enter the following code in the module. It will define a new function.
Public Function CountFor(ByVal calendarDate As Date, ByVal eventDates As Range) As Long
Dim dates As Variant
dates = eventDates.Value
'assert eventDates consists of 2 columns
Debug.Assert UBound(dates, 2) = 2
Const StartDateColumn = 1
Const EndDateColumn = 2
Dim result As Long
Dim eventIndex As Long
For eventIndex = LBound(dates, 1) To UBound(dates, 1)
If dates(eventIndex, StartDateColumn) <= calendarDate And dates(eventIndex, EndDateColumn) >= calendarDate Then result = result + 1
Next
CountFor = result
End Function
- Close the module and go back to the spreadsheet.
- Select a cell you want to store the value (cell F5 in our case) and insert the following formula:
=CountFor(DATE(90,1,1),C5:D24)
- Press Enter.
We can use VBA to our advantage to create a custom function and use it as many times as we want in the workbook to count date occurrences within a particular period in Excel.
Method 3 – Counting Unique Date Occurrences
Steps:
- Select a cell.
- Enter the following formula:
=UNIQUE(C5:C17)
- It will create an array with all the unique values from the range.
- To find the counts, select cell F5 and insert the following formula:
=COUNTIF($C$5:$C$17,E5)
- Press Enter.
- Select the cell again and click and drag the fill handle icon to the end of the unique values to replicate the formula for the rest of the cells.
Excel will provide us with all the unique dates and date occurrences of each one.
Download the Practice Workbook
You can download the workbook used for the demonstration from the link below.
Related Articles
<< Go Back to Excel COUNT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!