## Count records between two dates

*Article updated on October 07, 2017*

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:**

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

**Alternative array formula in cell D3:**

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

**Alternative formula:**

### 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.

### Download excel sample file for this tutorial.

count-records-between-two-dates-in-excel

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**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.
*

### 22 Responses to “Count records between two dates”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

This did a great job of giving me what I needed, with a minor tweak in that I took out the $ in $D$2 and $D$1 and replaced with D2 & D3, respectively.

In case someone might ask why, it easily replicates the formula multiple times by copy/paste methods. Otherwise, copying and pasting into the next cell to total the difference between the next two dates on a long list of dates will simply copy the same exact formula 30 timess. The $ make the cell range entered constant, ignoring Excel's internal mechanisms that would otherwise tell the formula to automatically adjust to the new formula location (d3&d2, d4&d3, d5&d4, etc.). You still want to use the ctrl+enter, though. I had about 300 rows with dates ranging over two months to work with.

Thanks so much!

Thanks for your comment!

Great job, I've seen some posts regarding this this subject but your SUMPRODUCT solution is the more elegant and versatile I've found!

Thumbs up and thanks!

Any chance this would work with multiple parameters.

I need to count how many times JA appears in a given period 1-Sep-10 and 10-Sep-10

A B

1 JA 2-Sep-10

2 SH 11-Sep-10

3 JA 3-Sep-10

I can't seem to link CountIf(A1:A3,"JA") and the Sum(If) part

any help greatly appreciated.

Sam,

read this post: Count records between two dates with multiple parameters in excel

thanks man, worked a treat

I can't seem to take this work. I am using the SUMPRODUCT formula. The only difference is that the dates to count are on another sheet within the workbook, but I would doubt that would be the issue. I keep getting the #NUM! error, but I have double verified that all reference cells are formated as dates. Please help.

Excel stores dates as a number representing the number of days since 1900-Jan-0 This is called a serial date, or serial date-time.

Source: https://www.cpearson.com/excel/datetime.htm

Are all referenced cell values serial dates?

How can i tell. I did read the article and played with the 1904 system indicator under options, but none of that made the formula work.

Marina,

Select a date cell.

Press Ctrl + 1.

Select "Category:" General.

If sample data shows you a number like 40494 (today) then you know it is a serial date.

Good work guys, but I require little different solution.

In A column, there are dates in mmddyyy format and in B column, there are two variables uses either "PASS" or "FIAIL". All I want to do is to count the "PASS" in individual month range.

Can someone help me in it. I am able to count days of month from the column A but can not link it with Column B.

Regards,

Johny

Janib Soomro,

Read this post: Count matching cells in date range in excel

thanks, I need to count in a list the interval between the same value, example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 so the answer must be for the value 1 the spaces are 2,2,6. thank you

jeyner,

read this post:

Excel: List intervals between two values

how do i count cells to determine how many cells occured on jan 2, 2011 from 2 am and 6 am.. which is housed in one of my columns in my excel workbook.

Very nice indeed! How would I go about averaging a column of data (instead of counting) based on a start and end date?

Nevermind. I found this link on your site, and just changed the MAX function with the AVERAGE function and it appears to be correctly working. Thanks again!!

Forgot the link in case others need it:

https://www.get-digital-help.com/2009/11/11/lookup-min-max-values-within-a-date-range-in-excel/

Tom,

Thanks, I am happy you found what you were looking for!

Hi Oscar,

Can this be applied using time as start and end parameter?

For example column A 12:01AM, column B 1:00AM

01:01AM 2:00AM and so on.

Thanks

How do I set up an IF,Then function/ or a macro (not sure what would be better) to do the following very complicated thing... I have a spreadsheet where I am monitoring KPI (dates of certain events listed by job)... there are 5 dates that are being monitored to make sure they fall within the expected cycle times for the company. So... I want to set up an IF, then formula that lists IF there is no date listed in a field then in another column (where I put the formula) I would like it to use my header's (for the missing field) as text, then count the dates outstanding from the day the job was opened (in column C) ie.=TODAY()-C2

I'm not sure how to set the formula, but I want the end result to look something like, "Estimate is 133 days past due" being pulled from the values "[blank field's column header] is [=(TODAY()-C2)-Cycle Time Expectation#)] past due"

Christina,

Can you provide some example data and the desired outcome or upload a workbook?