Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    COUNTIF function (2000/3)

    I have been unsuccessful in combining the AND function into the criteria of the COUNTIF function. The attached spreadsheet gives an example.
    In cell F3, I need a formula that counts the instances of "A" in Column "B" where the date in Column "A" is within the month of April 2005.
    I have hard coded F2:H4 with the solutions that the formula will be required to provide.

    Any guidance will be greatly appreciated.
    Regards
    Don

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

    Re: COUNTIF function (2000/3)

    You can't use COUNTIF with multiple criteria. Enter the following formula in F2:

    =SUMPRODUCT(($B$2:$B$27=RIGHT(F$1,1))*(YEAR($A$2:$ A$27)=YEAR($E2))*(MONTH($A$2:$A$27)=MONTH($E2)))

    then fill down to F4 and fill right to H4.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF function (2000/3)

    Hans
    Thanks for the speedy and comprehensive response. The solution solved my immediate problem; The rationale makes it unnecessary for me to ask a similar question in the future (providing my memory holds up). This kind of support is truly valuable.
    Regards
    Don

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF function (2000/3)

    =SUMPRODUCT(($A$2:$A$27-DAY($A$2:$A$27)+1=$F2)+0,($B$2:$B$27=RIGHT(F$1,1)+ 0)

    Where F2 houses the first day of the month

Posting Permissions

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