Counting Criteria in Cells in Multiple Ranges

[WalterK]

I am trying to count the number of times the number 1 appears in a cell in
the same row for multple columns. For example, say I have 4 columns, with
the cell being the number 1 or blank. I need to count every time the number
1 appears in two or more columns in the same row for 400 rows. It seems I
would need it to count using many ranges, from two to ten columns looking for
combinations, e.g., searching range A1:A400 looking for the number 1 and
searching range B1:B400 for the number one, etc., and when it identifies the
number 1 in the same row for 2 or more columns, it would count it. In the
example below the count would be 3 because there were three times when the
number 1 appeared in 2 columns in the same row Rows 1. 4 and 6).

A B C D
1 1 1
2 1
3 1
4 1 1
5 1
6 1 1

I would assume the formula could be expanded to search for a combintation of
"1's" over 10 columns, etc.

Thanks for your help and suggestions.

 

[Teethless mama]

=SUM(IF(((A1:A6=1)+(B1:B6=1)+(C1:C6=1)+(D16=1))>1,1))

ctrl+shift+enter, not just enter

 

[daddylonglegs]

Try

=SUMPRODUCT((SUBTOTAL(9,OFFSET(A11,ROW(A1:A400)-ROW(A1),0))>1)+0)

You can expand to a 10 column range by changing D1 to J1

 

[Max]

A simple & quick way ..
In E1, copied down: = --(COUNTIF(A11,1)>=2)
Then in F1: =COUNTIF(E:E,">0")
for the answer

 

[T. Valko]

For 4 columns:

=SUMPRODUCT(--(MMULT(--(A1400=1),{1;1;1;1})>1))

For 10 columns:

=SUMPRODUCT(--(MMULT(--(A1:J400=1),{1;1;1;1;1;1;1;1;1;1})>1))