count cells with year sets in a column?

Here's my problem - I ahe a short column of dates, different dates sorted
ascending from 2000 to 2006. Different dates. I am seeking (in vain so far) a
formula that will count the dates by last 4 digits, i.e. "2001" and give me
the count.

I tried a bunch of stuff so far with no effing luck. Like:

=COUNTIF(D2:D271,"*2001")
=SUMPRODUCT(D1:D271)="*2002" result: FALSE

In the past, I found out (after much research & wastage of temporal
resources) that Excel just won't do some stuff, and I suspect this is one of
those things.

Any suggestions? If you can answer this, I bow to your impressive knowledge!

Mark
aka Brainless (from working on worksheets too much)

Try

=SUMPRODUCT(--(YEAR(D1:D271)=2006))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Brainless_in_Boston" <[email protected]> wrote in
message news:[email protected]...
> Here's my problem - I ahe a short column of dates, different dates sorted
> ascending from 2000 to 2006. Different dates. I am seeking (in vain so
> far) a
> formula that will count the dates by last 4 digits, i.e. "2001" and give
> me
> the count.
>
> I tried a bunch of stuff so far with no effing luck. Like:
>
> =COUNTIF(D2:D271,"*2001")
> =SUMPRODUCT(D1:D271)="*2002" result: FALSE
>
> In the past, I found out (after much research & wastage of temporal
> resources) that Excel just won't do some stuff, and I suspect this is one
> of
> those things.
>
> Any suggestions? If you can answer this, I bow to your impressive
> knowledge!
>
> Mark
> aka Brainless (from working on worksheets too much)

Danged if you ain't rite!
My hats off to ya!
Yee-ha!

BTW, I know I can use COUNTA & eyeball it, but for bigger applications that
just is too tedious...

Thanks for your help - very much...

Mark

==============================




"Sandy Mann" wrote:

> Try
>
> =SUMPRODUCT(--(YEAR(D1:D271)=2006))
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Brainless_in_Boston" <[email protected]> wrote in
> message news:[email protected]...
> > Here's my problem - I ahe a short column of dates, different dates sorted
> > ascending from 2000 to 2006. Different dates. I am seeking (in vain so
> > far) a
> > formula that will count the dates by last 4 digits, i.e. "2001" and give
> > me
> > the count.
> >
> > I tried a bunch of stuff so far with no effing luck. Like:
> >
> > =COUNTIF(D2:D271,"*2001")
> > =SUMPRODUCT(D1:D271)="*2002" result: FALSE
> >
> > In the past, I found out (after much research & wastage of temporal
> > resources) that Excel just won't do some stuff, and I suspect this is one
> > of
> > those things.
> >
> > Any suggestions? If you can answer this, I bow to your impressive
> > knowledge!
> >
> > Mark
> > aka Brainless (from working on worksheets too much)
>
>
>

The problem with searching for 2001 in a date is that it does not exist! In
Excel a date is a just number, for example today is 38841 - it is a count of
the number of days since the start of 1900 - well almost, it would have
been that if 1900 had been a leap year but that's another story. Enter
today's date in a cell and then re-format it as General and you will see.
You therefore have to use the YEAR() function to create the number 2006 from
the *Date* number and use that number.

--
Regards


Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Brainless_in_Boston" <[email protected]> wrote in
message news:[email protected]...
> Danged if you ain't rite!
> My hats off to ya!
> Yee-ha!
>
> BTW, I know I can use COUNTA & eyeball it, but for bigger applications
> that
> just is too tedious...
>
> Thanks for your help - very much...
>
> Mark
>
> ==============================
>
>
>
>
> "Sandy Mann" wrote:
>
>> Try
>>
>> =SUMPRODUCT(--(YEAR(D1:D271)=2006))
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Brainless_in_Boston" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> > Here's my problem - I ahe a short column of dates, different dates
>> > sorted
>> > ascending from 2000 to 2006. Different dates. I am seeking (in vain so
>> > far) a
>> > formula that will count the dates by last 4 digits, i.e. "2001" and
>> > give
>> > me
>> > the count.
>> >
>> > I tried a bunch of stuff so far with no effing luck. Like:
>> >
>> > =COUNTIF(D2:D271,"*2001")
>> > =SUMPRODUCT(D1:D271)="*2002" result: FALSE
>> >
>> > In the past, I found out (after much research & wastage of temporal
>> > resources) that Excel just won't do some stuff, and I suspect this is
>> > one
>> > of
>> > those things.
>> >
>> > Any suggestions? If you can answer this, I bow to your impressive
>> > knowledge!
>> >
>> > Mark
>> > aka Brainless (from working on worksheets too much)
>>
>>
>>