Today I will show how you can count the number occurrences of a particular day in a range of dates and also the total number of days in a particular day range.

**Table of Contents**hide

**Download Practice Workbook**

**How to Count the Number of Occurrences in a Date Range in Excel**

Let us have a data set like this. We have the names and the publishing dates of some books of Sir H. Rider Haggard.

First I shall show how you can count the number of occurrences of a particular day in the range.

Then I shall show you how you can count the total number of days in a particular range.

**Number of Occurrences of a Particular Date**

Let us find out how many books were published on the date 18-Aug-1888. How can you find that?

Follow these steps.

**1. Using the COUNTIF() Function**

This is the simplest method.

**Syntax**

`=COUNTIF(range,criteria)`

- Takes two arguments, one range of cells called
**range,**and a particular criteria called**criteria.** - Returns the number of cells within that range which maintains the particular criteria.

If you want to know more about **COUNTIF()** Function, visit this link.

As you want to know how many dates equal to August 18,1888 are there in the range **C4** to **C23**, our formula will be

`=COUNTIF(C4:C23,"18-Aug-1888")`

You see, the total number of books published on August 18, 1888 is 3.

**2. Using SUMPRODUCT() Function**

**Syntax**

`=SUMPRODUCT(array1,[array2],array[3]...)`

- Takes one or more range of cells as arguments.
- Returns their mathematical sum as output.

If you want to know more about **SUMPRODUCT()** function, visit this link.

Our formula will be

`=SUMPRODUCT(--(C4:C23="18-Aug-1888"))`

- 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 where the date is**August 18, 1888**.

**3. Using Pivot Table**

If you want to know the number of occurrences of all the dates together, then you can use this method.

First select any cell from the column of the dates. Here I select cell** C10**.

Then go to **Insert>PivotTable** in Excel Toolbar under **Tables** section. Click the dropdown menu.

Select **PivotTable.**

You will get a box called **Create Pivot Table**.

In the Choose Where **You Want the Pivot Table to be Replaced** menu, check **Existing Worksheet.**

Then in the **Location** box, select the cell where you want the **Pivot Table**.

I choose cell **E3**.

Then click **OK**. You will get the Pivot Table in your selected cell.

In the Pivot Table, in the **Choose fields to add a report** menu, put a tick on the column you want to count. Here I want to count the column **Date of Publication**.

Then drag it to the **Row Labels** and **Values box.**

You will get the number of occurrences of each date in the** PivotTable**.

If you want, you can find out how many times each month or year occurred.

For that, select any cell in the **Row Labels** and double click it. From the options available, click on **Group**.

You will get the **Grouping** box like this.

Now select which one you want to see. For example, if you select month and click **OK**, you will get the number of times of occurrence of the months, like this.

**Total Number of Dates in a Particular Range**

Now if we want to find out how many books were published between January 01, 1881 and December 31, 1990, how can we achieve that?

Here are the methods.

**1. Using COUNTIFS() Function**

**Syntax**

`=COUNTIFS(range_1,criteria_1,[range_2],[criteria_2]...)`

- Takes one or more range of cells and one or more corresponding criteria.
- Returns the number of cells that fulfill all the criteria.

If you want to know more about** COUNTIFS()** function, visit this link.

Our formula here will be

`=COUNTIFS(C4:C23,">1-Jan-81",C4:C23,"<=31-Dec-90")`

See, the total number of books published between January 1, 1880 and December 31, 1890 is 14.

**2. Using SUMPRODUCT() Function**

To know details about **SUMPRODUCT()** function, go to section 2 of the Particular Date section.

Our formula here will be

`=SUMPRODUCT(((C4:C23)>=DATE(80,1,1))*((C4:C23)<=DATE(90,12,31)))`

See, we again get the same result.

The total number of books published between January 01, 1880 and December 12, 1890 is 14.

**3. Using VBA Code**

Now look at this data set. We have the same books of Sir H. Rider Haggard. But this time, we have two different columns.

One contains the starting days of writing the books.

And the other contains the finishing days.

Now if someone asks you how many books he was writing between January 1, 1890, then?

You can find it out using this **VBA** code.

```
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
```

- This site helped us understand and develop the code.
- To know how to save VBA codes, visit this post.
- This code creates an Excel function called
**CountFor().** **CountFor()**function takes two arguments, a date and a range of cells.- It returns how many events were going on on that particular day within that range.

Now Come back to the Excel Worksheet, select a cell and write this formula

`=CountFor(DATE(90,1,1),C4:D23)`

We see, he was writing seven books on January 1, 1890.

**Conclusion**

Using these methods, you can count the number of occurrences of any date in any range in Excel. Do you know any other method? Let us know in the comment section.