1

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.