Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Countif: three columns (xp)

    I have three columns containing integers between 0 and 8 (alternatively, a number of 3-clumn rows). I would like to count the number of rows where column 1 AND column 2 And Column 3 each contain a zero.

    0 0 0
    0 1 0
    0 0 0
    1 1 1

    The above example would evaluate to 2.

    TIA
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Countif: three columns (xp)

    This array formula should work, presuming your top left cell is A1:

    =SUM(((A1:A4+B1:B4+C1:C4)=0)*1)

    Enter using 'Ctrl-Shift-Enter'.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Countif: three columns (xp)

    Let's say your data are in columns A, B and C, and in rows 1 to 100. Try

    =SUMPRODUCT((A1:A100=0)*(B1:B100=0)*(C1:C100=0))

    as a normal formula, or

    =SUM((A1:A100=0)*(B1:B100=0)*(C1:C100=0))

    as an array formula (confirm with Ctrl+Shift+Enter).

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif: three columns (xp)

    John

    I have used your idea thus:

    =SUM(((AJ4:AJ192+AK4:AK192+AL4:AL192)=0)*1)

    but I'm getting a #VALUE error.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: Countif: three columns (xp)

    It's an array formula, so you must confirm it with Ctrl+Shift+Enter instead of just Enter.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Countif: three columns (xp)

    Does your formula, once entered, have curly parens (braces), like this:

    {=SUM(((AJ4:AJ192+AK4:AK192+AL4:AL192)=0)*1)}

    If not, re-enter the formula with Ctrl-Shift-Enter. If it does, then you probably have a non-numeric value in your range.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif: three columns (xp)

    Yes, it was entered correctly as an array formula so I assume the error lies in the data. I've got it sorted now. Thanks, John.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif: three columns (xp)

    Thanks, once again. Hans. Your first suggestion does exactly what I want.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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