Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count months (2000)

    Hi, if I had dates in A2:A200 wrote like this (3-Jan-06) on sheet 1 and on sheet 2 in a2:a12 I had the months wrote like this (January) what would the formula be on sheet 2 B2 letting me know who many people we see in the month of January, February and so on?
    Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count months (2000)

    Enter the following as an array formula in B2 (confirm with Ctrl+Shift+Enter instead of just Enter):
    <code>
    =SUM(1*(TEXT(Sheet1!$A$2:$A$200,"mmmm")=A2))
    </code>
    and fill down to B13 (I assume December is in A13, not A12)

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count months (2000)

    Hans,
    Would Sumif work?
    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count months (2000)

    You could use a countif:
    =COUNTIF(Sheet1!$A$2:$A$200,">="&DATEVALUE(A2&"1, 2006"))-COUNTIF(Sheet1!$A$2:$A$200,">"&DATE(2006,MONTH(DAT EVALUE(A2&" 1, 2006"))+1,1)-1)

    [It could be less complicated if instead of months in Sheet2!A2:A13 you used actual dates (could be formatted to display the month), or month numbers]
    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count months (2000)

    Since you are asking about SUMIF, is it safe to assume that there is another column with a patient count involved?

    The following assumes that column B has a patient count for each date in column A

    If your month labels are text:
    =SUMPRODUCT((TEXT(Sheet1!$A$2:$A$200,"mmmm")=A2)*S heet1!$B$2:$B$200)

    If you month labels are dates and formatted as sdckap mentions:
    =SUMPRODUCT((MONTH(Sheet1!$A$2:$A$200)=MONTH(H2))* Sheet1!$B$2:$B$200)

Posting Permissions

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