This post will guide you how to count cells between two dates with multiple criteria in excel. How do I count records between two dates and multiple criteria with formula in excel. You can easily count cells between two dates using COUNTIFS function. and if you want to count records between two dates with two or more criteria, how to achieve it.

1. Count Cells between Two Dates with Multiple Criteria

Assuming that you have a list of data that contain two columns, and one column contain product name and another column contain date values, and you want to count the number of product “excel” which is between two dates (from 2018/8/9-2018/10/19) in column B. You can create a new excel formula based on the SUMPRODUCT function to achieve the result.

=SUMPRODUCT(--($B$2:$B$7>=$D$2), --($B$2:$B$7<=$D$3), --($A$2:$A$7="excel"))

Type this formula into a blank cell and then press Enter key in your keyboard.

You will see that the count number will be shown.

If you want to count the number of the product “excel” and “word” which between two dates in column B, and you can refer to the above formula to write down another complex formula based on the SUMPRODUCT function and the COUNTIF function.

=SUMPRODUCT(--($B$2:$B$7>=$D$2), --($B$2:$B$7<=$D$3), --(COUNTIF($D$1:$E$1, $A$2:$A$7)))

Type this formula into a blank cell and press Enter key. you will get the result.

2. Count Cells between Two Dates Using a User-Defined Function

Now, let’s dive into the second method, using a user-defined function created with VBA code. This method is all about efficiency and automation, making it perfect for large datasets or recurring tasks.

Step1: Press Alt + F11 to open the VBA Editor.

Step2: In the VBA Editor, go to the “Insert” menu and select “Module.” This creates a space to write your VBA code.

Step3: Copy and paste the provided VBA code for the user-defined function into the module. Save and close the VBA Editor.

Function CountCriteriaBetweenDates(rngCriteria As Range, rngDates As Range, startDate As Date, endDate As Date, criteriaValue As Variant) As Long
    Dim cell As Range
    Dim count As Long
    count = 0

    ' Loop through each cell in the specified ranges
    For Each cell In rngDates
        ' Check if the cell value can be converted to a date and if the date is between the specified range
        If IsDate(cell.Value) Then
            Dim cellDate As Date
            cellDate = CDate(cell.Value)
            If cellDate >= startDate And cellDate <= endDate And rngCriteria.Cells(cell.Row, 1).Value = criteriaValue Then
                count = count + 1
            End If
        End If
    Next cell

    ' Return the count
    CountCriteriaBetweenDates = count
End Function

Step4: In a cell, enter a formula like this:

=CountCriteriaBetweenDates(A2:A7, B2:B7, D2, D3, D1)

3. Video: Count Cells between Two Dates with Multiple Criteria

This Excel tutorial video where we’ll tackle the challenge of counting cells between two dates while considering multiple criteria using both formula and User-defined function in Excel.

4. Related Functions

• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel SUMPRODUCT function
  The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…