Count instances of text in date entries

[MilusC]

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.

 

[Peo Sjoblom]

=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

 

[RagDyer]

Try this:

=SUMPRODUCT((B4:B40<>"")*(MONTH(B4:B40)=8))

 

[MilusC]

That worked! Thanks for the help.
--
Cal

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

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.

 

[RagDyer]

Wonder why?

Both formulas should perform the same.

 

[dec697]

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.

 

[T. Valko]

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,D421))

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)),D421))

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

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

If the range contains the text entry August:

=AVERAGE(IF(B4:B21="August",D421))

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

 

[dec697]

=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D421))

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

 

[T. Valko]

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)),D421))

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

 

[dec697]

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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