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

SUMPRODUCT Function in Excel

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.

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.

A data set in Excel

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")

COUNTIF() function in Excel

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.

SUMPRODUCT() Function in Excel

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.

PivotTable in Excel Toolbar

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.

PivotTable box in Excel

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.

PivotTable in Excel

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

PivotTable Data in Excel

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.

Pivot Table in options in Excel

You will get the Grouping box like this.

Grouping in Pivot Table

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.

Pivot Table in Excel

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")

COUNTIFS() Function in Excel

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

SUMPRODUCT Function in Excel

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)

VBA made function in Excel

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo