Counting dates

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<=C1))

or array enter (that is, enter by simultaneously pressing Ctrl+Shift+Enter):
=SUM((A2:A100>=B1)*(A2:A100<=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

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

Aladin

=======

Thank you Aladin, I toyed a little with how to do that last night but could not remember the syntax. Thanks again for the tip.

Tom U.

Thanks for your help!