Counting Criteria in Cells in Multiple Ranges
 

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.

Counting Criteria in Cells in Multiple Ranges
 

=SUM(IF(((A1:A6=1)+(B1:B6=1)+(C1:C6=1)+(D1:D6=1)) 1,1))

ctrl+shift+enter, not just enter


"WalterK" wrote:

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.

Counting Criteria in Cells in Multiple Ranges
 

Try

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

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

"Teethless mama" wrote:

=SUM(IF(((A1:A6=1)+(B1:B6=1)+(C1:C6=1)+(D1:D6=1)) 1,1))

ctrl+shift+enter, not just enter


"WalterK" wrote:

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.

Counting Criteria in Cells in Multiple Ranges
 

A simple & quick way ..
In E1, copied down: = --(COUNTIF(A1:D1,1)=2)
Then in F1: =COUNTIF(E:E,"0")
for the answer
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"WalterK" wrote:
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.

Counting Criteria in Cells in Multiple Ranges
 

For 4 columns:

=SUMPRODUCT(--(MMULT(--(A1:D400=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))

--
Biff
Microsoft Excel MVP


"daddylonglegs" wrote in message
...
Try

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

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

"Teethless mama" wrote:

=SUM(IF(((A1:A6=1)+(B1:B6=1)+(C1:C6=1)+(D1:D6=1)) 1,1))

ctrl+shift+enter, not just enter


"WalterK" wrote:

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.