Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    CountIf with two criteria doesn't count (97/XP)

    Most valuable members of this board, please hear this question...

    I have two columns B & C. I want to count all occurences of a value (e.g. 1) in column B where the corresponding value in column C = x.
    I use this formula (cell B317 in attached worksheet):
    =COUNT(IF(B3:B310=1;IF(G3:G310="x";B3:B310;0);0))
    The result remains zero, independent of the number of 'x' present...

    It must be something stupid I'm overlooking, but I can't figure out what. Any ideas?
    Thanks in advance!

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CountIf with two criteria doesn't count (97/XP

    You can use an array formula

    <code>=SUM((B2:B309=1)*(C2:C309="x"))</code>

    Array formulas need to be entered by using Control+Shift+Enter rather than just Enter.

    See attached.

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

    Re: CountIf with two criteria doesn't count (97/XP)

    Tony's formula will work well. An alternative using a "normal" (non-array) formula is:

    =SUMPRODUCT((B2:B309=1)*(C2:C309="x"))

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SOLVED Re: CountIf with two criteria doesn't count

    Hans, Tony,
    Thanks a lot - it works!
    Using summing functions for counting - MS logic at it's best <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15>... I'm glad to have you around!

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

    SOLVED Re: CountIf with two criteria doesn't count

    It works because in Excel, TRUE = 1 and FALSE = 0. You can use this in many situations.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SOLVED Re: CountIf with two criteria doesn't count

    Hans...
    a star for you... you answered my hidden question <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    Thank you for sharing... this will make it much easier to remember!

Posting Permissions

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