COUNTIF using multiple columns if one meets the criterion

[Airfix9]

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.

Ideas greatly received.

 

[barry houdini]

Try like this

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

 

[MrKowz]

Try:

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

 

[Airfix9]

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

Thanks for your swift help.

 

[barry houdini]

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"))