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)

count cells with year sets in a column?
 

Try

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Brainless_in_Boston" wrote in
message ...
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)

count cells with year sets in a column?
 

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


with @tiscali.co.uk


"Brainless_in_Boston" wrote in
message ...
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)

count cells with year sets in a column?
 

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


with @tiscali.co.uk


"Brainless_in_Boston" wrote in
message ...
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


with @tiscali.co.uk


"Brainless_in_Boston" wrote
in
message ...
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)