Results 1 to 7 of 7

Thread: Countif (XP)

  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Countif (XP)

    Is it possible to use a countif such as =countif(range not equal to "a123","b246","c357")? Range is to represent the column and cells for this example. What I am trying to do is find a method of counting various text at one time without having to use separate formulas for each example of text I am looking for.

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

    Re: Countif (XP)

    Try
    <code>
    =COUNTA(range)-SUM(COUNTIF(range,{"a123","b246","c357"})</code>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (XP)

    To expand on Hans' reply. If you want to be able to check one range against another range of cells you can use the following array formula (confirm with Ctrl+Shift+Enter)

    {=COUNTA(range)-SUM(COUNTIF(range,range2))}

    Where range2 is a range of cells that contain the "a123","b246","c357"

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (XP)

    Thanks for the reply.

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

    Re: Countif (XP)

    Assume the range of data located at : A1:A10, try to use the following formula :

    =SUM(COUNTIF(A1:A10,{"<>","a123","b246","c357"})*{ 1,-1,-1,-1})

    Regards
    Bosco

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (XP)

    Would you mind explaining the point of *[1,-1,-1,-1} Thanks.

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

    Re: Countif (XP)

    COUNTIF(A1:A10,"<>") returns the number of non-blank cells in A1:A10 (just like COUNTA). This value is multiplied with 1.
    COUNTIF(A1:A10,"a123") returns the number of cells equal to "a123". This value is multiplied with -1, so in the SUM it is in fact subtracted.
    Similar for the other two.
    So

    =SUM(COUNTIF(A1:A10,{"<>","a123","b246","c357"})*{ 1,-1,-1,-1})

    is equivalent to

    =COUNTIF(A1:A10,"<>")-COUNTIF(A1:A10,"a123")-COUNTIF(A1:A10,"b246")-COUNTIF(A1:A10,"c357")

Posting Permissions

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