# Thread: Countif: three columns (xp)

1. ## 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

2. ## 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'.

3. ## 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. ## 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.

5. ## Re: Countif: three columns (xp)

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

6. ## 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.

7. ## 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.

8. ## Re: Countif: three columns (xp)

Thanks, once again. Hans. Your first suggestion does exactly what I want.

#### Posting Permissions

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