excel formula- count if date is betwen two dates 4

[Bill4tektips]

I'm having a senior moment, can anyone give me the excel formula for counting cells in a column where the date falls between two dates. i.e. Cell shows 29/01/2007 and I want to count how many cells fall between 31/12/2006 and 31/01/2007

 

[SkipVought]

Hi,

Check out SUMPRODUCT...
[tt]
=SUMPRODUCT(--(DateRange>=FromRef)*(DateRange<=ToRef))
[/tt]


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[AnotherHiggins]

OR
[tab]=CountIf(A:A,"<31/01/2007")-CountIf(A:A,"<31/12/2006")

Or using cell references, which will make it easier to see and edit start and end dates:

[tab]=COUNTIF(A:A,"<"&C1)-COUNTIF(A:A,"<"&B1)
where C1 = 31/01/2007 and B1 = 31/12/2006


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi Bil4tektips:

In addition to fine contributions from Skip and John, here is another way using the DCOUNTA function ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

So many ways to skin that poor cat....


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.