Counting Dates-a large number of events

[Jerry]

I get a report every day that lists a large number of
events by date. There are many per date and I would like
a formula that counts the number of times a date is
repeated. I would also like to take it one step further
and count the number of times an event happens per date.
Any suggestions?

[duane]

suggest you look at countif function - easy to set up but exactly how depends on wheether your dates are dates or text.

[CLR]

=COUNTIF(A1:A26,"01/02/03") will tell you how many times the date 01/02/03
occurs in the range A1:A26

=SUMPRODUCT((A1:A26=1/2/3)+(B1:B26="event1")) will tell you how manytimes on
that date a corresponding "Event1" showed up in range B1:B26

Vaya con Dios,
Chuck, CABGx3


"Jerry" <[email protected]> wrote in message
news:[email protected]...
> I get a report every day that lists a large number of
> events by date. There are many per date and I would like
> a formula that counts the number of times a date is
> repeated. I would also like to take it one step further
> and count the number of times an event happens per date.
> Any suggestions?
>

[CLR]

Sorry, the first one is fine, but the second one fell apart with more
testing, and I'm about to fall off my chair right now, so can't go on
tonight........hopefully someone else will pick up on it for you, or I will
try again tomorrow......

Vaya con Dios,
Chuck, CABGx3


"CLR" <[email protected]> wrote in message
news:[email protected]...
> =COUNTIF(A1:A26,"01/02/03") will tell you how many times the date 01/02/03
> occurs in the range A1:A26
>
> =SUMPRODUCT((A1:A26=1/2/3)+(B1:B26="event1")) will tell you how manytimes
on
> that date a corresponding "Event1" showed up in range B1:B26
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Jerry" <[email protected]> wrote in message
> news:[email protected]...
> > I get a report every day that lists a large number of
> > events by date. There are many per date and I would like
> > a formula that counts the number of times a date is
> > repeated. I would also like to take it one step further
> > and count the number of times an event happens per date.
> > Any suggestions?
> >
>
>

[Biff]

Hi!

Dates in column A
Events in column B

=SUMPRODUCT(--(A1:A20=DATE(2005,1,1)),--(B1:B20="event"))

OR enter a date in C1:

=SUMPRODUCT(--(A1:A20=C1),--(B1:B20="event"))

Biff

>-----Original Message-----
>Sorry, the first one is fine, but the second one fell
apart with more
>testing, and I'm about to fall off my chair right now, so
can't go on
>tonight........hopefully someone else will pick up on it
for you, or I will
>try again tomorrow......
>
>Vaya con Dios,
>Chuck, CABGx3
>
>
>"CLR" <[email protected]> wrote in message
>news:[email protected]...
>> =COUNTIF(A1:A26,"01/02/03") will tell you how many
times the date 01/02/03
>> occurs in the range A1:A26
>>
>> =SUMPRODUCT((A1:A26=1/2/3)+(B1:B26="event1")) will tell
you how manytimes
>on
>> that date a corresponding "Event1" showed up in range
B1:B26
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>> "Jerry" <[email protected]> wrote in
message
>> news:[email protected]...
>> > I get a report every day that lists a large number of
>> > events by date. There are many per date and I would
like
>> > a formula that counts the number of times a date is
>> > repeated. I would also like to take it one step
further
>> > and count the number of times an event happens per
date.
>> > Any suggestions?
>> >
>>
>>
>
>
>.
>

[aroberts]

On the topic of dates, I have a simialr question;

I have a column of admission dates in the format dd/mm/yyyy.

These dates cover a range of several years.

I want to count all the dates for a given year eg count all 1999, count all 2000 etc. Each of these will be separate calculations. I simply need a sum of the amount of times each year occurs.

I have tried the COUNTIF function but can seem to get the year detected.

Any help would really be appreciated.

Alan

[duane]

=SUMPRODUCT((YEAR(A1:A1000)=2005)*1)

substitute your range for a1:a1000 and change the year for each calculation

[aroberts]

duane

Excellent and thank you, it is exactly what I wanted.

One question tho, why the *1? What does it do?

regards, Alan