In a previous article Count records between two dates in excel I counted dates that were between two dates in a column. In this article I will provide an array formula that counts dates in a range using a start date and end date as criteria.
In the range above (B3:G12) I have three columns containing dates and three adjacent columns containing some values.
There are two criteria in cell D14 and D15, the start and end date.
In cell F17, array formula counts total number of date records that meet start and end date criteria.
Array formula in F17:
How this formula works
--(B3:G12<=D15) and --(B3:G12>=D14) creates arrays where dates that meet the start or end date criteria results in 1 and those that don´t equals 0.
--(IF(tbl<>"", MOD(COLUMN(B3:G12), 2)=0, 0)) creates an array where values that are in a date column (B, D and F) equals 1 and values in C,E and G equals 0.
Sumproduct function returns the sum of the products of the corresponding ranges or arrays.
Download excel file for this tutorial.
Count date records between two dates in a range.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COLUMN(reference) returns the column number of a reference
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
- Most common value between two dates in excel
- Count records between two dates in excel
- Count unique distinct records in a date range
- Count unique records by date