I'm using Excel 2007. I have a column of dates in this format - 04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain month
shows up in the column, i.e. how many of the dates in the column are in Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

=SUMPRODUCT(--(ISNUMBER(B2:B40)),--(MONTH(B2:B40)=8))

The isnumber part is for blank cells since they are otherwise counted as
January
If you don't have any blank cells you can use

=SUMPRODUCT(--(MONTH(B2:B40)=8))

8 of course is the index number for August



--


Regards,


Peo Sjoblom

"MilusC" wrote in message
...
I'm using Excel 2007. I have a column of dates in this format - 04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain month
shows up in the column, i.e. how many of the dates in the column are in
Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

That worked! Thanks for the help.
--
Cal


"Peo Sjoblom" wrote:

=SUMPRODUCT(--(ISNUMBER(B2:B40)),--(MONTH(B2:B40)=8))

The isnumber part is for blank cells since they are otherwise counted as
January
If you don't have any blank cells you can use

=SUMPRODUCT(--(MONTH(B2:B40)=8))

8 of course is the index number for August



--


Regards,


Peo Sjoblom

"MilusC" wrote in message
...
I'm using Excel 2007. I have a column of dates in this format - 04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain month
shows up in the column, i.e. how many of the dates in the column are in
Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

Try this:

=SUMPRODUCT((B4:B40<"")*(MONTH(B4:B40)=8))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MilusC" wrote in message
...
I'm using Excel 2007. I have a column of dates in this format - 04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain month
shows up in the column, i.e. how many of the dates in the column are in
Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

Couldn't get this formula to give me the right answer. Maybe because there
are blank cells in the column. See other reply. Thank you.
--
Cal


"RagDyer" wrote:

Try this:

=SUMPRODUCT((B4:B40<"")*(MONTH(B4:B40)=8))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MilusC" wrote in message
...
I'm using Excel 2007. I have a column of dates in this format - 04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain month
shows up in the column, i.e. how many of the dates in the column are in
Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

Wonder why?

Both formulas should perform the same.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MilusC" wrote in message
...
Couldn't get this formula to give me the right answer. Maybe because there
are blank cells in the column. See other reply. Thank you.
--
Cal


"RagDyer" wrote:

Try this:

=SUMPRODUCT((B4:B40<"")*(MONTH(B4:B40)=8))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MilusC" wrote in message
...
I'm using Excel 2007. I have a column of dates in this format -
04-Aug-08,
15-Jul-08, etc. I would like to count the number of times a certain
month
shows up in the column, i.e. how many of the dates in the column are in
Aug,
how many in Jul, etc. I've tried:

=COUNTIF(B4:B40,"Aug")
=COUNTIF(B4:B40,"*Aug*")
=COUNTIF(B4:B40,"-Aug-")

none of these formulas works. Please help. Thanks.

I'm trying to do something similar, however, instead of counting the number
of dates with a particular month, I'm trying to average another column
providing the corresponding cell has the correct month. For example:

If B4:B40 equals august

Average D4 : D40

Can anybody help? thanks.

Does B4:B40 contain true Excel dates or does it contain the text entry
August?

Try one of these array formulas** :

For true Excel dates:

=AVERAGE(IF(MONTH(B4:B21)=8,D4:D21))

If you might need to get the average for January *and* there might be empty
cells within the range:

=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D 4:D21))

If you need the month of August for a specific year:

=AVERAGE(IF((MONTH(B4:B21)=1)*(YEAR(B4:B21)=2008), D4:D21))

If the range contains the text entry August:

=AVERAGE(IF(B4:B21="August",D4:D21))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"dec697" wrote in message
...
I'm trying to do something similar, however, instead of counting the
number
of dates with a particular month, I'm trying to average another column
providing the corresponding cell has the correct month. For example:

If B4:B40 equals august

Average D4 : D40

Can anybody help? thanks.