Countif formula
Countif formula
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Countif formula

  1. #1
    Guest

    Default

     
    I have one column of different date ranges (January through to December)

    I would like to use the countif formula to tell me how many occurrences of a date would fall into : Jan, Feb, Mar and so on.

    My main trouble is I need to specify a range of dates in the countif formula (i.e. coutif A1: A3243, where dates are between 01/01/02 and 31/01/02)

    Any thoughts !!!

    Graeme Bell

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,820
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-01 01:59, Anonymous wrote:
    I have one column of different date ranges (January through to December)

    I would like to use the countif formula to tell me how many occurrences of a date would fall into : Jan, Feb, Mar and so on.

    My main trouble is I need to specify a range of dates in the countif formula (i.e. coutif A1: A3243, where dates are between 01/01/02 and 31/01/02)

    Any thoughts !!!

    Graeme Bell
    Graeme,

    COUNTIF cannot handle such complicated (multiple) conditions.

    Lets say that A2:A30 houses the dates.

    In D2 on enter the list of months:

    {"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

    In E1 enter: 2002 [ the year of interest ]

    In E2 enter:

    =SUMPRODUCT((YEAR($A$2:$A$30)=$E$1)*(TEXT($A$2:$A$30,"mmm")=D2))

    Copy down this till the row of Dec.

    Aladin

  3. #3
    Guest

    Default

    Cant get this to work - It just brings up a zero

    I forwarded to your email a sample of the XL sheet, as i'm probably doing something stupid !!

    Graeme.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,820
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-01 05:09, Anonymous wrote:
    Cant get this to work - It just brings up a zero

    I forwarded to your email a sample of the XL sheet, as i'm probably doing something stupid !!

    Graeme.
    Graeme,

    When you see a list like

    {"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

    [ as the one I specified (they are called constant arrays in Excel) ],

    copy it,

    activate the start cell mentioned (I said D2 I believe),

    go to the Formula Bar,

    type =,

    paste what you copied,

    hit enter,

    activate the start cell,

    select from that cell on an area as big as the list mentioned (in your case 12 for the months of the year),

    hit control+shift+enter,

    copy selected range,

    do a Edit|Pate Special >values.

    So you have the specified items in Excel without typing them yourself.

    PS. I think the intend of this part of instruction was the main hurdle for you to get the formula work (BTW, see your mail).

    Aladin


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com