Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Countif's (2007)

    Loungers, further to my previous thread, if have this formula that I thought was working fine, =COUNTIF(Sheet2!$C$4:$C$24,Sheet1!B7), however following further testing, I note that it only counts exact matches, the date cell format is mmm-yy, I need the value to return a count of all occurrences that equal jan-08 for example, regardless of the the day.

    I hope that makes sense - any thoughts?

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

    Re: Countif's (2007)

    You'll have to use SUMPRODUCT to do this as Countif allows for only one criteria to be checked. I'm assuming Countif works the same in 2007 is the same as it does in 2k.

    =SUMPRODUCT((YEAR(Sheet2!$C$4:$C$24)=YEAR(Sheet1!B 7))*(MONTH(Sheet2!$C$4:$C$24)=MONTH(Sheet1!B7)))

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Countif's (2007)

    Cheers - will give it a shot.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Countif's (2007)

    There is a COUNTIFS (and SUMIFS) function in 2007 but I don't think you can use a function like MONTH(A1:A10) as the range argument so you would either need to use the SUMPRODUCT formula as in prior versions or use a start and end date for each month with COUNTIFS.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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