This article will focus on how to count date occurrences in Excel from different datasets and different conditions.

**Table of Contents**hide

## Excel Count Date Occurrences: 2 Suitable Examples

We are categorizing all the examples in different sections and subsections. That way, it will be easier to understand. In the first, section, we have demonstrated how you can count particular date occurrences in Excel. The second one includes counting occurrences in a particular range. And finally, we have shown how we can count unique date occurrences in Excel.

### 1. Number of Occurrences of a Particular Date

For this first case, we will focus on the following dataset.

The dataset consists of a list of works by H. Ridder Haggard and their publication dates. In the following subsections, we will see how we can count the number of occurrences on a particular date using Microsoft Excel’s different functions.

#### 1.1 Using COUNTIF Function

This is the simplest way to count occurrences on a particular date in Excel. The main idea is to use **the COUNTIF function** to do the work for us. Moreover, we can do it for any type of value, not just dates.

However, the **COUNTIF **function takes two arguments – one range of cells called range, and particular criteria called criteria. Then it returns the number of cells within that range which maintains the particular criteria.

For the dataset, the steps would be as follows.

**Steps:**

- First, select cell
**E5**. - Then write down the following formula in it.

`=COUNTIF(C5:C243,"18-Aug-1888")`

- After that, press
**Enter**.

This is how we can easily count occurrences of a particular date in Excel using the **COUNTIF **function.

#### 1.2 Using SUMPRODUCT Function

Another way we can achieve the same result is to utilize **the SUMPRODUCT function**. In particular, this function takes one or more range of cells as arguments. It consequently returns their mathematical sum as output. Hence we can use the function to configure a formula to count date occurrences in Excel.

Follow these steps to see how we can use it for this dataset.

**Steps:**

- First, select cell
**E5**. - Then write down the following formula in int.

`=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 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 1’s and 0’s. This is the total number of cells that has the date **August 18, 1888**.

- Finally, press
**Enter**on your keyboard.

Owing to the formula, this function will now return the total date occurrence of that particular input from the Excel spreadsheet.

#### 1.3 Using Pivot Table

If you want to count the number of occurrences of every date together, then you can use Excel’s **Pivot Table **feature. This is a powerful tool in Excel, that we can use to calculate, summarize and analyze data. And by summarizing, we can count the total date occurrences for every date in a dataset.

To use this method for the dataset or similar dataset, you need to follow these steps.

**Steps:**

- First, select any cell from the dataset.
- Then go to the
**Insert**tab on your ribbon. - Next, select
**PivotTable**from the**Tables**group section.

- Additionally, you can make sure of the dataset range and where you want to put the pivot table in the next box. To demonstrate, we have chosen a new worksheet.

- Then click on
**OK**. Owing to the previous choice, a new spreadsheet will open now. - Now go to the
**PivotTable Fields**that you will find on the right side of the spreadsheet containing the pivot table. - Next, click and drag the
**Date of Publication**to both**Rows**and**Values**fields individually.

- As a result, the pivot table will appear in the desired place.

### 2. Total Number of Dates in a Particular Range

In this section, we will continue to count date occurrences in Excel. But this time, we will count the dates that belong to a range instead of a single match like in the previous section.

To demonstrate the methods, we will use the following dataset.

There is a change in the dataset. Although the main idea is the same as the previous one, some functions and formulas do not function properly with dates after the year 1901. And thus such change in the dataset.

Either way, you can follow one of these methods to count the date occurrences in a particular range by one of these three ways.

#### 2.1 Using COUNTIFS Function

In this subsection, we will utilize **the COUNTIFS function** to count date occurrences from a particular range in Excel. This function takes several arguments- always a range and a condition in pairs. And then it returns the number of cells that represents all the given conditions. So we can easily employ this function to our advantage.

Follow these steps to see how we can do that.

**Steps:**

- First, select cell
**E5**. - Then write down the following formula.

`=COUNTIFS(C5:C17,">1/1/1940",C5:C17,"<=12/31/1950")`

- Finally, press
**Enter**.

So we can see there are a total of three books that range from the year 1940 to 1950. And this is how we can count date occurrences from a particular range in Excel.

#### 2.2 Using SUMPRODUCT Function

Another way we can do the same is to use the **SUMPRODUCT **function. Generally, we use this function to find the sum of the products of several arrays. And we put these arrays as arguments of the function.

Now let’s suppose we want to find the same result- books published between 1940 and 1950. So what we can do is find the arrays of the books that are published after 1940 and before 1950 and then use the **SUMPRODUCT **function to find the sum of the product of them. Which in turn will give us the total number of books or dates that occurred within that period.

Follow these steps to see how we can implement that.

**Steps:**

- First, select cell
**E5**. - Second, insert the following formula in the cell.

`=SUMPRODUCT(((C5:C17)>=DATE(1940,1,1))*((C5:C17)<=DATE(1950,12,31)))`

- Third, 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

And the final method to count the date occurrences from a particular range would be the usage of a VBA code. Microsoft’s Visual Basic for Applications (VBA) is an event-driven programming language that we can use for various purposes- starting from simple cell entries and modifications to automate large and dull processes.

In this section, we will talk about the code that will help us count dates that belong to a range. But before using any kind of VBA code, you first need the **Developer **tab to show on your ribbon. If you don’t have one already, click here to see how to display the **Developer** tab on your ribbon.

Before we dive into the details of the process, let’s prepare the dataset for the process. This is plainly to showcase the length of the code, although you can use them with the same datasets above. We are showing the application for the following dataset with a date range for each book.

Once you have the tab, you can follow these steps to count date occurrences that belong to a particular range within Excel.

**Steps:**

- First, go to the
**Developer**tab on your ribbon. - Then select
**Visual Basic**from the**Code**group section.

- As a result, the VBA window will open up.
- Now select the
**Insert**tab in it. - Then select
**Module**from the drop-down menu.

- If the module isn’t already selected, select it.
- Next, insert 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
```

- Now close the module and go back to the spreadsheet.
- After that, 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)`

- Finally, press
**Enter**.

Thus 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.

### 3. Count Unique Date Occurrences

As part of counting the number of dates, we will now demonstrate a method to count all the unique dates and the number of occurrences for each one in Excel. We are gonna need this method for a dataset like this.

As you can see, there are some repetitive dates. We are gonna count exactly what dates are there and how many times. We are gonna need the usage of **UNIQUE** and **COUNTIF** functions for that.

Follow these steps to see how we can utilize them.

**Steps:**

- First, let’s find out the unique dates. For that, select cell
- Then write down the following formula in it.

`=UNIQUE(C5:C17)`

- As a result, it will create an array with all the unique values from the range.

- Now to find the counts, select cell
**F5**and insert the following formula.

`=COUNTIF($C$5:$C$17,E5)`

- Then press
**Enter**.

- After that, 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.

As a result, Excel will provide us with all the unique dates and date occurrences of each one.

**Download Practice Workbook**

You can download the workbook used for the demonstration from the link below.

## Conclusion

So this was all about counting the number of dates. Hopefully, you have grasped the idea and can apply them to count date occurrences for your circumstances in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.