Results 1 to 8 of 8
Thread: Countif: three columns (xp)

20050307, 21:13 #1
 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 3clumn 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>

20050307, 21:26 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 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 'CtrlShiftEnter'.John ... I float in liquid gardens
UTC 7ąDS

20050307, 21:28 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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).

20050307, 21:34 #4
 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>

20050307, 21:37 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Countif: three columns (xp)
It's an array formula, so you must confirm it with Ctrl+Shift+Enter instead of just Enter.

20050307, 21:39 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 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, reenter the formula with CtrlShiftEnter. If it does, then you probably have a nonnumeric value in your range.John ... I float in liquid gardens
UTC 7ąDS

20050307, 22:04 #7
 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>

20050307, 22:05 #8
 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>