Count occurrences of dates "George Gee" <georgegee[ at ]nomaps.com> 12/29/2008 4:19:41 PM Column A is a list of dates with the format 01/01/2000, 30/04/2001 etc. How would I count the number of occurrences of dates in column A for the month of say January 2000 How would I count the number of occurrences of dates for the year 2000 Great If you can help. George Gee

Re: Count occurrences of dates barry houdini <barry.houdini[ at ]virgin.net> 12/29/2008 4:31:02 PM Hello George, For January 2000 you could use a formula like =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) for the whole year =SUMPRODUCT(--(YEAR(A1:A100)=2000)) extend the range as necessary but you can't use the whole column unless you have Excel 2007

Re: Count occurrences of dates "George Gee" <georgegee[ at ]nomaps.com> 12/29/2008 4:47:14 PM Hi Barry Thanks for your reply The first part for the month works fine. The second part for the year, I'm getting #VALUE! Only 4500 rows Thanks again George Gee "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message news:9a57401b-2558-4efd-a372-db43dad70011[ at ]q30g2000prq.googlegroups.com... [Quoted Text] > Hello George, > > For January 2000 you could use a formula like > > =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) > > for the whole year > > =SUMPRODUCT(--(YEAR(A1:A100)=2000)) > > extend the range as necessary but you can't use the whole column > unless you have Excel 2007

Re: Count occurrences of dates "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> 12/29/2008 5:33:33 PM Barry's formula is interesting. I would have used =SUMPRODUCT(--(YEAR(A1:A100)=2000),--(MONTH(A1:A100)=1)) You can see how this is similar to =SUMPRODUCT(--(YEAR(A1:A100)=2000)) Odd that one works and the other does not. I wonder if one or more cells do not have real dates. You say you have 4500 rows. What is the result from =COUNT(A1:A4500) ? If every cell has a real date value, the result should be 4500. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "George Gee" <georgegee[ at ]nomaps.com> wrote in message news:On7bbUdaJHA.1704[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > Hi Barry > > Thanks for your reply > The first part for the month works fine. > The second part for the year, I'm getting #VALUE! > > Only 4500 rows > > Thanks again > George Gee > > > "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message > news:9a57401b-2558-4efd-a372-db43dad70011[ at ]q30g2000prq.googlegroups.com... >> Hello George, >> >> For January 2000 you could use a formula like >> >> =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) >> >> for the whole year >> >> =SUMPRODUCT(--(YEAR(A1:A100)=2000)) >> >> extend the range as necessary but you can't use the whole column >> unless you have Excel 2007 > >

Re: Count occurrences of dates barry houdini <barry.houdini[ at ]virgin.net> 12/29/2008 5:58:29 PM YEAR function will fail if any entry in the range is text that can't be coerced to a number, if row 1 has a header then exclude that row. If that still doesn't work you could try TEXT again, i.e. =SUMPRODUCT(--(TEXT(A1:A100,"yyyy")="2000")) but you may want investigate whether you have some entries that should be dates but are, in fact, text

Re: Count occurrences of dates "George Gee" <georgegee[ at ]nomaps.com> 12/29/2008 6:16:50 PM Hi Bernard Thanks for your reply, I've got it sorted now, hundreds of entries that are just a letter "a", I'm replacing these gradually with a date. Thanks again George Gee "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> wrote in message news:u1CTTudaJHA.1704[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > Barry's formula is interesting. I would have used > =SUMPRODUCT(--(YEAR(A1:A100)=2000),--(MONTH(A1:A100)=1)) > You can see how this is similar to > =SUMPRODUCT(--(YEAR(A1:A100)=2000)) > > Odd that one works and the other does not. I wonder if one or more cells > do not have real dates. > You say you have 4500 rows. What is the result from =COUNT(A1:A4500) ? If > every cell has a real date value, the result should be 4500. > best wishes > -- > Bernard V Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > remove caps from email > > "George Gee" <georgegee[ at ]nomaps.com> wrote in message > news:On7bbUdaJHA.1704[ at ]TK2MSFTNGP05.phx.gbl... >> Hi Barry >> >> Thanks for your reply >> The first part for the month works fine. >> The second part for the year, I'm getting #VALUE! >> >> Only 4500 rows >> >> Thanks again >> George Gee >> >> >> "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message >> news:9a57401b-2558-4efd-a372-db43dad70011[ at ]q30g2000prq.googlegroups.com... >>> Hello George, >>> >>> For January 2000 you could use a formula like >>> >>> =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) >>> >>> for the whole year >>> >>> =SUMPRODUCT(--(YEAR(A1:A100)=2000)) >>> >>> extend the range as necessary but you can't use the whole column >>> unless you have Excel 2007 >> >> > >

Re: Count occurrences of dates "George Gee" <georgegee[ at ]nomaps.com> 12/29/2008 6:20:49 PM Hi Barry Thanks for this, very many entries just a letter "a". Your most recent formula does the trick. Thanks again George Gee "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message news:f04bb2c9-6a90-4927-ad9f-020117f3d6c0[ at ]40g2000prx.googlegroups.com... [Quoted Text] > YEAR function will fail if any entry in the range is text that can't > be coerced to a number, if row 1 has a header then exclude that row. > > If that still doesn't work you could try TEXT again, i.e. > > =SUMPRODUCT(--(TEXT(A1:A100,"yyyy")="2000")) > > but you may want investigate whether you have some entries that should > be dates but are, in fact, text

Re: Count occurrences of dates "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> 12/29/2008 6:57:46 PM We learn something every day. Thanks, Barry -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message news:f04bb2c9-6a90-4927-ad9f-020117f3d6c0[ at ]40g2000prx.googlegroups.com... [Quoted Text] > YEAR function will fail if any entry in the range is text that can't > be coerced to a number, if row 1 has a header then exclude that row. > > If that still doesn't work you could try TEXT again, i.e. > > =SUMPRODUCT(--(TEXT(A1:A100,"yyyy")="2000")) > > but you may want investigate whether you have some entries that should > be dates but are, in fact, text

RE: Count occurrences of dates Shane Devenshire 12/29/2008 9:40:01 PM Hi, You could use a pivot table for this with the Date field in the Row area and as a count in the Data area. Then group the row area by month and year. -- If this helps, please click the Yes button Cheers, Shane Devenshire "George Gee" wrote: [Quoted Text] > Column A is a list of dates with the format 01/01/2000, 30/04/2001 etc. > How would I count the number of occurrences of dates in column A > for the month of say January 2000 > > How would I count the number of occurrences of dates for the year 2000 > > Great If you can help. > > George Gee > > >

Re: Count occurrences of dates Shane Devenshire 12/29/2008 9:46:03 PM Hi, By the way the pivot table solution will automatically detect text date verses real dates and group them differently, alerting you to a problem. Also the pivot table automatically will run subtotals by year and month. The month subtotals will automatically appear as the count. To add the year subtotal select the year field in the pivot table and choose Pivot Table, Subtotals. -- If this helps, please click the Yes button Cheers, Shane Devenshire "George Gee" wrote: [Quoted Text] > Hi Bernard > > Thanks for your reply, I've got it sorted now, hundreds of entries > that are just a letter "a", I'm replacing these gradually with a date. > > Thanks again > George Gee > > > "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> wrote in message > news:u1CTTudaJHA.1704[ at ]TK2MSFTNGP05.phx.gbl... > > Barry's formula is interesting. I would have used > > =SUMPRODUCT(--(YEAR(A1:A100)=2000),--(MONTH(A1:A100)=1)) > > You can see how this is similar to > > =SUMPRODUCT(--(YEAR(A1:A100)=2000)) > > > > Odd that one works and the other does not. I wonder if one or more cells > > do not have real dates. > > You say you have 4500 rows. What is the result from =COUNT(A1:A4500) ? If > > every cell has a real date value, the result should be 4500. > > best wishes > > -- > > Bernard V Liengme > > Microsoft Excel MVP > > http://people.stfx.ca/bliengme > > remove caps from email > > > > "George Gee" <georgegee[ at ]nomaps.com> wrote in message > > news:On7bbUdaJHA.1704[ at ]TK2MSFTNGP05.phx.gbl... > >> Hi Barry > >> > >> Thanks for your reply > >> The first part for the month works fine. > >> The second part for the year, I'm getting #VALUE! > >> > >> Only 4500 rows > >> > >> Thanks again > >> George Gee > >> > >> > >> "barry houdini" <barry.houdini[ at ]virgin.net> wrote in message > >> news:9a57401b-2558-4efd-a372-db43dad70011[ at ]q30g2000prq.googlegroups.com... > >>> Hello George, > >>> > >>> For January 2000 you could use a formula like > >>> > >>> =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) > >>> > >>> for the whole year > >>> > >>> =SUMPRODUCT(--(YEAR(A1:A100)=2000)) > >>> > >>> extend the range as necessary but you can't use the whole column > >>> unless you have Excel 2007 > >> > >> > > > > > > >