Count instances of text in date entries

  • Thread starter Thread starter MilusC
  • Start date Start date
M

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.
 
=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
 
That worked! Thanks for the help.
--
Cal


Peo Sjoblom said:
=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
Click to expand...
 
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.
 
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)),D4: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)
 
=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D4:D21))

I used the above formula and it didn't work.
 
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)),D4:D21))

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