# Countif with an array?

#### Spartin

##### New Member
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>

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Spartin

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### New Member
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

##### MrExcel MVP
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))

Replies
8
Views
538
Replies
4
Views
426
Replies
0
Views
370
Replies
8
Views
183
Replies
3
Views
458

Threads
1,191,043
Messages
5,984,315
Members
439,882
Latest member
gerdc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back