COUNT formula help
 

Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count the
number of times an event occurs in March 2007, March 2006, etc. The dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

One way (for March 2007) is
=COUNTIF(A1:A400,"="&DATE(2007,3,1))-COUNTIF(A1:A400,"="&DATE(2007,4,1))
--
David Biddulph

"GeorgeG" wrote in message
...
Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count the
number of times an event occurs in March 2007, March 2006, etc. The dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

Here is another way (for March 2007)....

=SUMPRODUCT(--(DATE(2007,3,DAY(A1:A400))=A1:A400))

Rick


"GeorgeG" wrote in message
...
Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count the
number of times an event occurs in March 2007, March 2006, etc. The dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count the
number of times an event occurs in March 2007, March 2006, etc. The dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count the
number of times an event occurs in March 2007, March 2006, etc. The dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

Define "a month ago". For example, what **date** would you consider a month
ago from today? Also, what **date** would you consider a month ago if
today's date were October 31, 2007?

Rick


"Ken" wrote in message
...
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that
lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count
the
number of times an event occurs in March 2007, March 2006, etc. The
dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

A mnth ago would fall btwn today and 30 days prior today.

"Rick Rothstein (MVP - VB)" wrote:

Define "a month ago". For example, what **date** would you consider a month
ago from today? Also, what **date** would you consider a month ago if
today's date were October 31, 2007?

Rick


"Ken" wrote in message
...
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that
lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn 3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an event
occurs for any given month, each year. For exmaple, i'd like to count
the
number of times an event occurs in March 2007, March 2006, etc. The
dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

Okay, just to be clear then, a month ago from March 1, 2007 would be January
30, 2007 (skipping February entirely), correct?

Are you using the same 30-day month for the other spans (3 months ago would
be 90 days prior to today; 6 month ago would be 180 days prior to today,
etc.)?

Rick


"Ken" wrote in message
...
A mnth ago would fall btwn today and 30 days prior today.

"Rick Rothstein (MVP - VB)" wrote:

Define "a month ago". For example, what **date** would you consider a
month
ago from today? Also, what **date** would you consider a month ago if
today's date were October 31, 2007?

Rick


"Ken" wrote in message
...
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that
lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn
3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater
than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an
event
occurs for any given month, each year. For exmaple, i'd like to
count
the
number of times an event occurs in March 2007, March 2006, etc. The
dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

Yes, right.

"Rick Rothstein (MVP - VB)" wrote:

Okay, just to be clear then, a month ago from March 1, 2007 would be January
30, 2007 (skipping February entirely), correct?

Are you using the same 30-day month for the other spans (3 months ago would
be 90 days prior to today; 6 month ago would be 180 days prior to today,
etc.)?

Rick


"Ken" wrote in message
...
A mnth ago would fall btwn today and 30 days prior today.

"Rick Rothstein (MVP - VB)" wrote:

Define "a month ago". For example, what **date** would you consider a
month
ago from today? Also, what **date** would you consider a month ago if
today's date were October 31, 2007?

Rick


"Ken" wrote in message
...
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries that
lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3. btwn
3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater
than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an
event
occurs for any given month, each year. For exmaple, i'd like to
count
the
number of times an event occurs in March 2007, March 2006, etc. The
dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers

COUNT formula help
 

Give these a try...

Within 30 days:
=SUMPRODUCT((A1:A100<=TODAY())*(A1:A100TODAY()-30))

Between 30 days and 90 days:
=SUMPRODUCT((A1:A100<=TODAY()-30)*(A1:A100TODAY()-90))

Between 90 days and 180 days:
=SUMPRODUCT((A1:A100<=TODAY()-90)*(A1:A100TODAY()-180))

Earlier than 180 days:
=SUMPRODUCT((A1:A100<=TODAY()-180)*(A1:A100<""))

Adjust the ranges to match your actual data span.

Rick


"Ken" wrote in message
...
Yes, right.

"Rick Rothstein (MVP - VB)" wrote:

Okay, just to be clear then, a month ago from March 1, 2007 would be
January
30, 2007 (skipping February entirely), correct?

Are you using the same 30-day month for the other spans (3 months ago
would
be 90 days prior to today; 6 month ago would be 180 days prior to today,
etc.)?

Rick


"Ken" wrote in message
...
A mnth ago would fall btwn today and 30 days prior today.

"Rick Rothstein (MVP - VB)" wrote:

Define "a month ago". For example, what **date** would you consider a
month
ago from today? Also, what **date** would you consider a month ago if
today's date were October 31, 2007?

Rick


"Ken" wrote in message
...
David, Rick, Smiley,
My problem is similar. I need to calculate the count of entries
that
lie-- 1.
btwn today and 1 mnth ago, 2. btwn 1 mnth ago and 3 mnths ago, 3.
btwn
3
mnths ago and 6 mnths ago, 4. btwn 6 and 9 mnths ago and 5. greater
than 9
mnths ago.
All entries have a date associated with entry in colmn C.
Thanks ahead of tme.


"Teethless mama" wrote:

=SUMPRODDUCT(--(MONTH(A2:A100)=3),--(YEAR(A2:A100)=2007))
or
=SUMPRODUCT(--(TEXT(A2:A100,"m/yy")="3/07"))


"GeorgeG" wrote:

Hi there,

I'm trying to create a formula that counts the number of times an
event
occurs for any given month, each year. For exmaple, i'd like to
count
the
number of times an event occurs in March 2007, March 2006, etc.
The
dates
stored on my spreadsheet are in the form of dd/mm/yyyy.

Any help would be greatly appreciated!
Cheers