Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Count a specific month in a column

Help,

Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.

I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.

What am I doing wrong - please help
--
Thanks for your support
Elaine
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,101
Default Count a specific month in a column

Maybe something like this
=COUNTIF(A1:A5,"july")

"Elaine" wrote:

Help,

Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.

I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.

What am I doing wrong - please help
--
Thanks for your support
Elaine

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Count a specific month in a column

Hi Mike

I have tried that it appears as zero, so I tried it with as a wildcard and
no no luck still appears as zero

All the column has is this:

17/07/2008
15/07/2008
18/02/1900
18/02/1900
07/07/2008
18/02/1900
04/07/2008

Trying to pull out the number of July Performance reviews

--
Thanks for your support
Elaine


"Mike" wrote:

Maybe something like this
=COUNTIF(A1:A5,"july")

"Elaine" wrote:

Help,

Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.

I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.

What am I doing wrong - please help
--
Thanks for your support
Elaine

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Count a specific month in a column

Try this:

=SUMPRODUCT((MONTH(A1:A7)=7)*(YEAR(A1:A7)=2008))

Hope this helps.

Pete

On Jul 1, 1:30*am, Elaine wrote:
Hi Mike

I have tried that it appears as zero, so I tried it with as a wildcard and
no no luck still appears as zero

All the column has is this:

17/07/2008
15/07/2008
18/02/1900
18/02/1900
07/07/2008
18/02/1900
04/07/2008

Trying to pull out the number of July Performance reviews

--
Thanks for your support
Elaine



"Mike" wrote:
Maybe something like this
=COUNTIF(A1:A5,"july")


"Elaine" wrote:


Help,


Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.


I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.


What am I doing wrong - please help
--
Thanks for your support
Elaine- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Count a specific month in a column

Got it to work by using =SUMPRODUCT(--(TEXT(C12:C18,"mmm")="Jul"))
--
Thanks for your support
Elaine


"Mike" wrote:

Maybe something like this
=COUNTIF(A1:A5,"july")

"Elaine" wrote:

Help,

Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.

I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.

What am I doing wrong - please help
--
Thanks for your support
Elaine



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Count a specific month in a column

or...
=SUMPRODUCT(--(month(C12:C18)=7))

or for a given year:
=SUMPRODUCT(--(TEXT(C12:C18,"yyyymm")="200807"))





Elaine wrote:

Got it to work by using =SUMPRODUCT(--(TEXT(C12:C18,"mmm")="Jul"))
--
Thanks for your support
Elaine

"Mike" wrote:

Maybe something like this
=COUNTIF(A1:A5,"july")

"Elaine" wrote:

Help,

Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.

I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.

What am I doing wrong - please help
--
Thanks for your support
Elaine


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Count a specific month in a column

Thanks
Pete got it before I got your message, have now added the year on, working
perfectly now
--
Thanks for your support
Elaine


"Pete_UK" wrote:

Try this:

=SUMPRODUCT((MONTH(A1:A7)=7)*(YEAR(A1:A7)=2008))

Hope this helps.

Pete

On Jul 1, 1:30 am, Elaine wrote:
Hi Mike

I have tried that it appears as zero, so I tried it with as a wildcard and
no no luck still appears as zero

All the column has is this:

17/07/2008
15/07/2008
18/02/1900
18/02/1900
07/07/2008
18/02/1900
04/07/2008

Trying to pull out the number of July Performance reviews

--
Thanks for your support
Elaine



"Mike" wrote:
Maybe something like this
=COUNTIF(A1:A5,"july")


"Elaine" wrote:


Help,


Been working on this spreadsheet and it is starting to drive me Mad, I am
trying to count a column which it contains text and numbers, I am trying to
count every cell that contains the month July with no success.


I have tried wild cards and a pivot table, this give me a total of 6 when
there is only 4.


What am I doing wrong - please help
--
Thanks for your support
Elaine- Hide quoted text -


- Show quoted text -



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Count a specific month in a column

You're welcome, Elaine - thanks for feeding back.

Pete

On Jul 1, 1:55*am, Elaine wrote:
Thanks
Pete got it before I got your message, have now added the year on, working
perfectly now
--
Thanks for your support
Elaine


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count occurance of a month in a column of dates? Stormy Excel Worksheet Functions 1 August 30th 07 06:44 PM
count number of dates in a column that are within a month period.. Sarah J Excel Worksheet Functions 7 May 24th 07 06:53 PM
Formula to count month-specific items IoHeFy Excel Discussion (Misc queries) 4 January 4th 07 02:45 PM
URGENT: Search for text in column and count by month saras Excel Discussion (Misc queries) 1 April 13th 06 03:00 AM
SUM/COUNT column(s) based on specific value present within the column markx Excel Worksheet Functions 6 March 22nd 05 11:23 AM


All times are GMT +1. The time now is 12:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"