Google-sheets – COUNTIF multiple columns not counting values matching both criterias

google sheets

Sheet1

Country     Activity1   Activity2
Spain       2           2
Spain       0           0
Italy       1           0
France      1           0
Germany     0           0
Italy       4           2
Germany     1           0
Belgium     0           0
France      0           1

Sheet 2

Country     Count # >1
Spain
France
Italy
Belgium
Germany

I'm trying to count in Sheet2 the number of occurrences in Sheet1!ColA if they have >1 in activity 1 OR 2.

I can't do:

COUNTIFS('Sheet1'A2:A,'Spain','Sheet1'!B2:B,">1")+COUNTIFS('Sheet1'A2:A,'Spain','Sheet1'!C2:C,">1")

Because that would count twice if the number is more than 1 for both Activity1 and Activity2.

Best Answer

I would use filter and count the results. 

=counta(filter(Sheet1!A2:A, (Sheet1!A2:A="Spain")*((Sheet1!B2:B>1)+(Sheet1!C2:C>1))))

The second argument of filter is a Boolean expression: * means AND, + means OR. One can create an arbitrarily complex condition in this way.

After the countries are filtered, counta counts them.

Instead of hard-coding "Spain" in the formula, you probably want a reference there, such as Sheet1!A2:A=A2

Related Topic