Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem.

Yellow cells are user input cells.

How do I automatically count records in a specific date range?

Array formula in cell D3:

=SUM(IF((\$A\$2:\$A\$10<\$D\$2)*(\$A\$2:\$A\$10>\$D\$1), 1, 0)) + Ctrl + Shift + Enter

This is an array formula so make sure you press Ctrl + Shift + Enter.

Alternative array formula in cell D3:

=SUMPRODUCT(--(\$A\$2:\$A\$10<\$D\$2),--(\$A\$2:\$A\$10>\$D\$1)) + ENTER

If you want to count the start date and end date also, try this formula:

=SUM(IF((\$A\$2:\$A\$10<=\$D\$2)*(\$A\$2:\$A\$10>=\$D\$1),1,0)) + Ctrl + Shift + Enter

Alternative formula:

=SUMPRODUCT(--(\$A\$2:\$A\$10<=\$D\$2),--(\$A\$2:\$A\$10>=\$D\$1)) + ENTER

### Explaining alternative formula in cell D3

=SUMPRODUCT(--(\$A\$2:\$A\$10<=\$D\$2),--(\$A\$2:\$A\$10>=\$D\$1))

Step 1 - Create a boolean array with matching dates to the first criterion

=SUMPRODUCT(--(\$A\$2:\$A\$10<=\$D\$2),--(\$A\$2:\$A\$10>=\$D\$1))

--(\$A\$2:\$A\$10<=\$D\$2)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}<=39463)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}<=39463)

becomes

--({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE})

becomes

{1;1;1;0;1;0;1;1;1}

Step 2 - Create a boolean array with matching dates to the second criterion

=SUMPRODUCT(--(\$A\$2:\$A\$10<=\$D\$2),--(\$A\$2:\$A\$10>=\$D\$1))

--(\$A\$2:\$A\$10>=\$D\$1)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}>=39452)

becomes

--({FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})

becomes

{0;0;0;1;1;1;1;1;1}

Step 3 - All together

=SUMPRODUCT(--(\$A\$2:\$A\$10<=\$D\$2),--(\$A\$2:\$A\$10>=\$D\$1))

becomes

=SUMPRODUCT({1;1;1;0;1;0;1;1;1},{0;0;0;1;1;1;1;1;1})

becomes

=SUMPRODUCT({0;0;0;0;1;0;1;1;1})  returns 4.

count-records-between-two-dates-in-excel
(Excel 97-2003 Workbook *.xls)

SUM(number1,[number2],)
Adds all the numbers in a range of cells

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

This blog article is one out of  four articles on the same subject.

### Related posts:

Count unique records between two dates in excel

Count date records between two dates in a range in excel

Count records between two dates with multiple parameters in excel

How to count unique distinct records in a date range

Count unique distinct records with a date and column criteria in excel 2007