COUNT formula help

[Guest]

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

 

[David Biddulph]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Guest]

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

 

[Guest]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

Yes, right.

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

 

[Rick Rothstein \(MVP - VB\)]

Give these a try...

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

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

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

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

Adjust the ranges to match your actual data span.

Rick