# COUNTIF using multiple columns if one meets the criterion

#### Airfix9

##### Well-known Member
Hi all,

Having a senior moment.

What I have is a list of outlets in column A and then, in columns B, C and D a 1 if they stock a particular model (one column for each model).

What I want to do is to count the number of times that an outlet stocks at least one of the models. That is to say, if the sum of columns B, C and D is 1 or more, count it.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try like this

=SUMPRODUCT((B2:B10+C2:C10+D2:D10>0)+0)

Try:

=SUMPRODUCT(--((B1:B100+C1:C100+D1:D100)>=1))

Thanks guys. I adapted Mr Kowz's formula as follows:

=SUMPRODUCT(--((INDIRECT("'"&A9&"'!B3:B1000")+INDIRECT("'"&A9&"'!C3:C1000")+INDIRECT("'"&A9&"'!D3:D1000"))>=1)*--(INDIRECT("'"&A9&"'!A3:A1000")<>""))

Where A9 contains the country name of the sheet I am looking at and I then used the final look at column A to avoid the totals (the number of rows could change but would never be more than 1000).

Your formula should work OK as it is...... but note that it's bad practice to use *-- in any formula

Either use a syntax like

=SUMPRODUCT((A2:A10="x")*(B2:B10="y"))

or

=SUMPRODUCT(--(A2:A10="x"),--(B2:B10="y"))

Replies
1
Views
142
Replies
1
Views
161
Replies
4
Views
214
Replies
5
Views
554
Replies
20
Views
536

1,219,807
Messages
6,150,348
Members
450,952
Latest member
Zung

### 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.

### Which adblocker are you using?

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

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