Using Sum If and an array?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonathan - 06 May 2008 20:27 GMT

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

Reply to this Message

Jonathan - 06 May 2008 20:30 GMT

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.

> Hi all,
>
[quoted text clipped - 27 lines]
> 22 X X
> 23 X X X

Reply to this Message

Reitanos - 06 May 2008 20:38 GMT

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 <Jonat...@discussions.microsoft.com>
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.
[quoted text clipped - 30 lines]
> > 22 X X
> > 23 X X X

Reply to this Message

Zack Barresse - 06 May 2008 20:45 GMT

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.

Signature

Zack Barresse

> 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
[quoted text clipped - 40 lines]
>> > 22 X X
>> > 23 X X X

Reply to this Message

Zack Barresse - 06 May 2008 20:41 GMT

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.

Signature

Zack Barresse

> Hi all,
>
[quoted text clipped - 29 lines]
> 22 X X
> 23 X X X

Reply to this Message

Jonathan - 06 May 2008 20:48 GMT

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

> Hi there,
>
[quoted text clipped - 39 lines]
> > 22 X X
> > 23 X X X

Reply to this Message