The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Thanks Fred, it works too.
Do you have an answer for my last post ( probably went before yours)
How can I copy down adding a day everytime?
ex your formula 2008,1,1
2008,1,2
2008,1,3 etc, so I dont have to write the formula
every date/day.

"Fred Smith" wrote:

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

I assume you got your answer from Biff, but if now, use:
=countif(a:a,date(2008,1,row(b1)))

and copy down.

Regards,
Fred.

"Excelfan" wrote in message
...
Thanks Fred, it works too.
Do you have an answer for my last post ( probably went before yours)
How can I copy down adding a day everytime?
ex your formula 2008,1,1
2008,1,2
2008,1,3 etc, so I dont have to write the formula
every date/day.

"Fred Smith" wrote:

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts
with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any
B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this dates
to
days.
Thanks.
1/5/8
1/7/8

=COUNTIF(A1:A5000,"1/01/2008")
Yes, I could find it going thru another alike formula =countif(A1:A20,today())
Thanks anyways.

"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Now to copy down this formula adding a day everytime I'll need your help,
Instead of writting the same formula everytime and just changing the date to
be
count.Thanks.

"Excelfan" wrote:

=COUNTIF(A1:A5000,"1/01/2008")
Yes, I could find it going thru another alike formula =countif(A1:A20,today())
Thanks anyways.

"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

On Jan 18, 5:04*pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Hi Steven, Thanks for your involment and help. However it was my mistake when
I said about DAYS. Forget DAYS. I really wanted to count how many times a DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008), really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any
B cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this dates
to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

P.S.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in
any B cell
and then the same formula( of course not exactly "same" but alike)
will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this
dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Nope.
It gives me zero at all times for some reason.

"T. Valko" wrote:

P.S.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in
any B cell
and then the same formula( of course not exactly "same" but alike)
will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this
dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Sure - If you examine Smith's formula, it count all occurances of 1 January
2008, regardless of format.

My formula make no assumption on the decade
1 january 1998
1 january 1988
will also be counted if they are in the column.

The sumproduct simply checks that the year ends in an 8.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Cool. Thanks.

"Gary''s Student" wrote:

Sure - If you examine Smith's formula, it count all occurances of 1 January
2008, regardless of format.

My formula make no assumption on the decade
1 january 1998
1 january 1988
will also be counted if they are in the column.

The sumproduct simply checks that the year ends in an 8.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8