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.
Table of Contents
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],…)…