How to Count Date Occurrences in Excel (3 Methods)

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.

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.

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 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 issued between 1940 and 1950.

2.2 Using the SUMPRODUCT 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.

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.