Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Value with 2 criteria (2003)

    Loungers I need to be able to count the number of occurrence that meet 2 criteria. Column A contains text and column B numbers. I want to be able to count the occurrence where say column A contains the word "test" and column B contains the number 1 (for example) any ideas?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Value with 2 criteria (2003)

    Dean,

    Use and array formula like (Assumes the list is in a2:b2000 - edit as appropriate)

    =sum((a2:a2000='test')*(b2:b2000=1))

    Remember that the array formula is entered with ctrl-shift-enter not just enter. If you do it correctly Excel will add { } around the formula.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Value with 2 criteria (2003)

    Thanks for the quick rely kieran - will give it a shot

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Value with 2 criteria (2003)

    If column A contains only the word "test"

    Two other options are :

    =COUNT(IF(A1:A100="test",IF(B1:B100=1,1)))

    (Array formula confirmed with ctrl-shift-enter )

    or

    =SUMPRODUCT(--(A1:A100="test"),--(B1:B100=1))


    If column A contains the word " testA, testB, testC..."

    Then, the formula will be :

    =SUMPRODUCT(--(SEARCH("test",A1:A100)),--(B1:B100=1))


    Regards

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Value with 2 criteria (2003)

    Thanks bosco_yip will try this also

    Regards

Posting Permissions

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