Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Count If - please help

  1. #1
    Guest

    Default

    Hallo
    I am trying to do a count if - possible array formula for the following

    In cells B2:b10 there are names of people
    In cells C2:c10 there are ages 1 to 5

    I want to count the number of people whose name is "Ben" and their age is great than 10

    i tried {=count(if(b2:b10="Ben",if(c2:c10>10,c2:c10,0),0))}
    but it doesn't seem to be working

    Thanks
    Pete

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put in D2 =B2&C2 and then drag fill down

    Then use that range

    =COUNTIF(D2:D10,"Ben10")

    Regards,

    Gary Hewitt-Long

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    The DCOUNT or DCOUNTA is ideal for this and better than an array formula in many ways.

    Let's say you have the headings "Names" and "Ages", copy these to say cells D1:E1 in D2 put "Ben" and in E2 put 10

    =DCOUNTA(A1:B500,"Names",D1:E2)

    To see some more uses of these functions you can download some examples here:

    http://www.ozgrid.com/download/default.htm
    under DFunctionsWithValidation.zip



  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-03 21:35, Anonymous wrote:
    Hallo, I am trying to do a count if - possible array formula for the following
    In cells B2:b10 there are names of people
    In cells C2:c10 there are ages 1 to 5
    I want to count the number of people whose name is "Ben" and their age is great than 10
    I tried {=count(if(b2:b10="Ben",if(c2:c10>10,c2:c10,0),0))} but it doesn't seem to be working.
    Thanks, Pete.
    Maybe, this is easier:
    =SUMPRODUCT((B2:B10="Ben")*(C2:C10>10))

Some videos you may like

User Tag List

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
  •