Results 1 to 9 of 9

COUNTIF with multiple criteria

This is a discussion on COUNTIF with multiple criteria within the Excel Questions forums, part of the Question Forums category; How can one use COUNTIF with a combination of two or more criteria (using OR, AND etc)? For example: =COUNTIF(A1:A100 ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default COUNTIF with multiple criteria

    How can one use COUNTIF with a combination of two or more criteria (using OR, AND etc)? For example:

    =COUNTIF(A1:A100 , "AND( >10 , <20 ) " )

    does not work, but there must be a way to count the values that lie between 10 and 20.

  2. #2
    Board Regular
    Join Date
    Apr 2003
    Posts
    75

    Default Re: COUNTIF with multiple criteria

    Think you need to use a CSE/Array formula. ie
    =if(A1:A100>10,if(A1:A100<20,count(A1:A100)))

    And then hit Control + Shift and Enter!! (this bit is the array formula bit!)

  3. #3
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default Re: COUNTIF with multiple criteria

    very common question on the board - use sumproduct:

    =SUMPRODUCT((A1:A100>10)*(A1:A100<20))


    hth
    kevin

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Posts
    75

    Default Re: COUNTIF with multiple criteria

    Of course your way is probably better!

  5. #5
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: COUNTIF with multiple criteria

    ... or even

    =COUNTIF(A1:A100,">10")-COUNTIF(A1:A100,">=20")
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  6. #6
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default Re: COUNTIF with multiple criteria [Thanks!]

    Thanks for all your inputs. I tried the SUMPRODUCT and it worked.

    -Best Regards,
    S.T.

  7. #7
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default Re: COUNTIF with multiple criteria

    Good - just so you know, the * operator is used to include any value that meets the first criteria AND the second criteria. if you want to include values that meet EITHER of the criteria in your formula, use the + operator


    kevin

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: COUNTIF with multiple criteria

    Quote Originally Posted by kskinne
    Good - just so you know, the * operator is used to include any value that meets the first criteria AND the second criteria. if you want to include values that meet EITHER of the criteria in your formula, use the + operator
    kevin
    Careful, you don't want to use + with this SUMPRODUCT function 'cause it'll provide an incorrect count when both condtions are TRUE (e.g., TRUE+TRUE = 2). In that case it's best to use IF in an array formula...

    {=COUNT(IF((range1=value1)+(range2=value2),1))}

  9. #9
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default Re: COUNTIF with multiple criteria

    Yes it will, my apologies if I was unclear in my post - I was only including it as an additional piece of info for future reference, in case he needed to use sumproduct in a different problem with different circumstances.

    Regards
    kevin

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