Counting if between date range

[Guest]

I have a column of dates such as 8/5/06, I would like to create a formula
that counts the number of dates for a particular month. For example, if the
column reads as such:
9/13/06
8/28/06
9/20/06
9/20/06
8/20/06
9/22/06
9/22/06

I would like the formula to tell me that there are 2 for the month of
August. Then I could also set up another similar formular to tell me that
there are 5 for the month of September

 

[Guest]

Something like this might be what you're looking for.........

=COUNTIF(A:A,">=9/1/06")-COUNTIF(A:A,">=10/1/06")

Vaya con Dios,
Chuck, CABGx3

 

[Trevor Shuttleworth]

Marc

one way:

=SUMPRODUCT( --(G1:G7>=DATE(2006,8,1)),-- (G1:G7<=DATE(2006,8,31)) )
=SUMPRODUCT( --(G1:G7>=DATE(2006,9,1)),-- (G1:G7<=DATE(2006,9,30)) )

assuming the dates are in cells G1 to G7

Regards

Trevor

 

[Guest]

One way:

=SUMPRODUCT(--(MONTH(B1:B20)=8))

=SUMPRODUCT(--(MONTH(B1:B20)=9))

HTH

 

[Biff]

Here's one way to get all 12 months (assuming the year is the same):

Assuming your data is in the range A1:A7.

Enter this formula for the month name in say, E1:

=TEXT(DATE(2006,ROWS($1:1),1),"mmmm")

Enter this formula for the count in F1:

=SUMPRODUCT(--(MONTH(A$1:A$7)=ROWS($1:1)))

Select both E1 and F1 then copy down to row 12.

Biff

 

[Bernie Deitrick]

Marc,

=SUMPRODUCT(--(MONTH(A1:A1000)=8))
=SUMPRODUCT(--(MONTH(A1:A1000)=9))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you Chuck, I knew it should be an easy formula but the brain just
wasn't clicking.

Thanks again!

 

[Guest]

Happy to help, Marc...........thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3