Count instances of text in date entries
 

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.

Count instances of text in date entries
 

=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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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.

Count instances of text in date entries
 

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

I used the above formula and it didn't work.

Count instances of text in date entries
 

There's a typo in that formula. It's testing for January.

For August, array entered** :

=AVERAGE(IF((MONTH(B4:B21)=8)*(ISNUMBER(B4:B21)),D 4: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
...
=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D 4:D21))

I used the above formula and it didn't work.

Count instances of text in date entries
 

Ah, the problem was I didn't use ctrl, shift and enter. Thanks very much.

"T. Valko" wrote:

There's a typo in that formula. It's testing for January.

For August, array entered** :

=AVERAGE(IF((MONTH(B4:B21)=8)*(ISNUMBER(B4:B21)),D 4: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
...
=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D 4:D21))

I used the above formula and it didn't work.

Count instances of text in date entries
 

You're welcome!

--
Biff
Microsoft Excel MVP


"dec697" wrote in message
...
Ah, the problem was I didn't use ctrl, shift and enter. Thanks very much.

"T. Valko" wrote:

There's a typo in that formula. It's testing for January.

For August, array entered** :

=AVERAGE(IF((MONTH(B4:B21)=8)*(ISNUMBER(B4:B21)),D 4: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
...
=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D 4:D21))

I used the above formula and it didn't work.