|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
July 18th, 2008, 07:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 
|
|
#2
July 18th, 2008, 07:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#3
July 18th, 2008, 08:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#4
July 18th, 2008, 08:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#5
July 18th, 2008, 08:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#6
July 18th, 2008, 09:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#7
August 1st, 2008, 11:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
|
#8
August 2nd, 2008, 12:16 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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  21))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 21))If you need the month of August for a specific year: =AVERAGE(IF((MONTH(B4:B21)=1)*(YEAR(B4:B21)=2008), D4 21))If the range contains the text entry August: =AVERAGE(IF(B4:B21="August",D4 21))** 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.
|
|
#9
August 2nd, 2008, 03:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count instances of text in date entries
=AVERAGE(IF((MONTH(B4:B21)=1)*(ISNUMBER(B4:B21)),D 4
21)) I used the above formula and it didn't work.
|
|
#10
August 2nd, 2008, 05:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 21))** 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 21))I used the above formula and it didn't work.
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
