# COUNTIF multiple columns not counting values matching both criterias

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.

## 1 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`