Countif with an array?

[Spartin]

Good Afternoon:

I am trying to write a formula that will count items in a column that have met one or more criteria in corresponding column, but without counting those items twice. A simple to table to illustrate is below. As it is laid out, I would look to count 4 total items (A, B, C, E) since they meet one or more of the criteria. D would not be counted. In my real life data set, the columns are marked with a "1" just like the illustration below.

Any suggestions?

Thanks

Item	Criterion 1	Criterion 2	Criterion 3	Criterion 4
A		1
B	1
C		1		1
D
E	1	1	1

<tbody>
</tbody>

 

[Spartin]

Here is a little bit better illustration with cell references

A	B	C	D	E	F
1	Item	Criterion 1	Criterion 2	Criterion 3	Criterion 4
2	A	1
3	B		1
4	C	1	1
5	D
6	E		1	1	1

<tbody>
</tbody>

 

[Aladin Akyurek]

Re: Here is a little bit better illustration with cell references

If we had this:

Item	Criterion 1	Criterion 2	Criterion 3	Criterion 4
A	1
A			1
B		1
C	1	1
D
E		1	1	1

<tbody>
</tbody>

would the count still equal to 4?

 

[Spartin]

Re: Here is a little bit better illustration with cell references

The count would still equal 4, but that scenario does not exist. The item's are unique, 8-digit order numbers. Thanks.

 

[Spartin]

I found a solution on another website. For reference:

Array formula = SUM(N(MMULT(N(C2:F6=1),TRANSPOSE(COLUMN(C2:F6)^0))>0))

Thanks Google!

 

[Aladin Akyurek]

Re: Here is a little bit better illustration with cell references

The count would still equal 4, but that scenario does not exist. The item's are unique, 8-digit order numbers. Thanks.

Control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(B2:E6)+0,TRANSPOSE(COLUMN(B2:E6)^0)),1))