# How to Count Date Occurrences in Excel Today I will show how you can count the number of occurrences of a particular day in a range of dates and also count the total number of days in a particular day range.

## How to Count Date Occurrences 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.

### Case 1: 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?

#### i. 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.

#### ii. Using SUMPRODUCT() Function

Syntax

`=SUMPRODUCT(array1,[array2],array...)`
• Takes one or more range of cells as arguments.
• Returns their mathematical sum as output.

If you want to know more about the 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. #### iii. 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. ### Case 2: 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.

#### i. 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.

#### ii. Using SUMPRODUCT() Function

To know details about the 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.

#### ii. 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.  