Using Sum If and an array?
 

Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule. In the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X

Using Sum If and an array?
 

By the way, if it makes it easier, the Xs are actually 1s. I don't know if
this makes using some sort of sum function easier.

"Jonathan" wrote:

Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule. In the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X

Using Sum If and an array?
 

If you only need a count, you could add a helper formula in a column
after your data that counts the 1s like =IF(COUNT(B2:F2)1,1,0) and
then where ever you want to display the actual count you could just
sum that range.


On May 6, 3:30 pm, Jonathan
wrote:
By the way, if it makes it easier, the Xs are actually 1s. I don't know if
this makes using some sort of sum function easier.

"Jonathan" wrote:
Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule. In the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X

Using Sum If and an array?
 

Hi there,

You could use something like this...

=SUMPRODUCT(--(((B2:B24=1)+(C2:C24=1)+(D2:D24=1)+(E2:E24=1)+(F2: F24=1))1))

It would not, however, identify which items (IDs) would quantify that
return. To do that you would probably be best off with a helper column.
You could also do this in a pivot table.

--
Zack Barresse



"Jonathan" wrote in message
...
Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule. In
the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc.
Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X

Using Sum If and an array?
 

Even though the requirements of the OP was for "one cell" formula, I agree
about the helper column. It opens the door to many other possibilities in
regards to identification and such.

--
Zack Barresse



"Reitanos" wrote in message
...
If you only need a count, you could add a helper formula in a column
after your data that counts the 1s like =IF(COUNT(B2:F2)1,1,0) and
then where ever you want to display the actual count you could just
sum that range.


On May 6, 3:30 pm, Jonathan
wrote:
By the way, if it makes it easier, the Xs are actually 1s. I don't know
if
this makes using some sort of sum function easier.

"Jonathan" wrote:
Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule.
In the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc.
Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X

Using Sum If and an array?
 

Nope, only count needed and this did the trick--thanks!

"Zack Barresse" wrote:

Hi there,

You could use something like this...

=SUMPRODUCT(--(((B2:B24=1)+(C2:C24=1)+(D2:D24=1)+(E2:E24=1)+(F2: F24=1))1))

It would not, however, identify which items (IDs) would quantify that
return. To do that you would probably be best off with a helper column.
You could also do this in a pivot table.

--
Zack Barresse



"Jonathan" wrote in message
...
Hi all,

I have the data below and want to do the following: In ONE cell, I want
there to be a count of the IDs that have an X in more than one rule. In
the
case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc.
Any
help? Thanks!

ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5
1 X X
2 X
3 X X
4
5 X
6 X
7 X X
8 X
9 X
10 X
11
12
13 X X
14
15 X
16 X
17
18 X
19 X
20 X
21 X
22 X X
23 X X X