Counting dates

Posted by Paul on January 04, 2002 6:34 PM

I need a formula to count how many times the dates in A2:A100 = the year 2001. Thanks

Posted by Tom Urtis on January 04, 2002 8:49 PM

Here is one way to solve this, with 2 formula choices.

In cell B1, enter the date 01/01/01.
In cell C1, enter the date 12/31/01.

In cell D1 or anywhere you want, enter either:
=SUMPRODUCT((A2:A100>=B1)*(A2:A100&LT;=C1))

or array enter (that is, enter by simultaneously pressing Ctrl+Shift+Enter):
=SUM((A2:A100>=B1)*(A2:A100&LT;=C1))

Either of those formulas will return the number of times a date in the range A2:A100 falls within the year 2001.

One note, array formulas are not familiar to most casual Excel users, so to keep things as simple as possible, all things being equal, you're probably better off with the first formula option.

Tom Urtis

Posted by Aladin Akyurek on January 05, 2002 1:57 AM

Tom --

Using the YEAR function, the formulas can be shortened.

> Here is one way to solve this, with 2 formula choices.

> In cell D1 or anywhere you want, enter either:
> =SUMPRODUCT((A2:A100>=B1)*(A2:A100&LT;=C1))

=SUMPRODUCT((YEAR(A2:A100)=2001)+0)

where a cell can also be used if that cell houses the value 2001.

> or array enter (that is, enter by simultaneously pressing Ctrl+Shift+Enter):
> =SUM((A2:A100>=B1)*(A2:A100&LT;=C1))

{=SUM((YEAR(A2:A100)=2001)+0)}

> Either of those formulas will return the number of times a date in the range A2:A100 falls within the year 2001.

Yep.