Really counting DATES

[Excelfan]

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

 

[Fred Smith]

Try it like this:

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

Regards,
Fred

 

[Excelfan]

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

 

[Steven]

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 Bcell
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

 

[Excelfan]

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.

 

[Gary''s Student]

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.

 

[Excelfan]

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.

 

[Excelfan]

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.

 

[Excelfan]

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]

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.

 

[T. Valko]

Try it like this:

B1:

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

 

[T. Valko]

P.S.

Copy down as needed.

 

[Excelfan]

Cool. Thanks.

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.

 

[Excelfan]

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

 

[Fred Smith]

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.

 

[T. Valko]

Hmmm...

If this works:

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

Then this has to work:

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

Because it's essentially the same as:

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