Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT IF (XP / EXCEL)

    Your attachment seemed to be empty.
    Assuming that you have a bunch of rows with names, and a bunch of columns with attributes such as age and eating preferences
    The short answer is that COUNTIF doesn't do it because it can only handle one condition at a time.

    One easy way around this is to create a new column that is equal to your desired condition and count that
    e.g. in Z2 the formula =AND(B2="Apples",C2=21) would return a result that could be counted.

    There are also tricks with array formulae
    Finally Pivot Tables are a good general way to do this kind of cross-relationship with 'database' data.

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

    Re: COUNT IF (XP / EXCEL)

    A generic approach to multi-conditional counting,

    =SUMPRODUCT(--(AgeRange=21),--(FruitRange="apples"))

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNT IF (XP / EXCEL)

    Post deleted by jgreggcl

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

    Re: COUNT IF (XP / EXCEL)

    The workbook you attached is completely empty. Please create a workbook with some contents. You can then edit your post (click the <IMG SRC=http://www.wopr.com/w3timages/edit.gif> button in the post header), tick the 'Delete attachment' check box, and specify the path and filename of the corrected workbook. Please do NOT start a new thread for the same question.

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

    Re: COUNT IF (XP / EXCEL)

    One possible formula is

    =SUMPRODUCT((B2:B5=21)*(C2:C5="APPLE"))

    Another possibility is

    =SUM(IF((B2:B5=21)*(C2:C5="APPLE"),1,0))

    entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT IF (XP / EXCEL)

    Its best to post to the group so that others can benefit from questions and answers.

    Attached is your example extended with a new column, and also with a new sheet holding a pivot table.

    I see that others have covered the SUMPRODUCT and Array methods that are also commonly used, so won't repeat. The choice often depends on your particular needs at the time.

  7. #7
    New Lounger
    Join Date
    Jun 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT IF (XP / EXCEL)

    Thank you so much

    That was exactly what I wanted

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT IF (XP / EXCEL)

    Please do not delete your posts if there is not a reason to do so, particularly if there are replies to the post. It makes it very difficult for loungers to understand what the message thread is all about.
    Legare Coleman

Posting Permissions

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