Google Sheets: COUNTIF in multiple columns but only ONCE if more than 1 in same row

Question

ColA            ColB            ColC            ColD            ColE
DATE            COUNTRY 1       COUNTRY 2       COUNTRY 3       COUNTRY 4
01/xx/2017      INDONESIA       GERMANY         PHILIPPINES     PAKISTAN
01/xx/2017      MOROCCO         MOROCCO         MOROCCO         ITALY
23/xx/2017      USA             UK              NETHERLANDS     MOROCCO
23/xx/2017      MOROCCO         TANZANIA        AUSTRALIA       SWEDEN

What formula can I use to count the occurrences of a country (let's say Morocco) in ColB:ColE but only counting it ONCE if more than an occurrence appear in the same row? In this case for example the result should be 3.

Answer 1

Try this:

=ArrayFormula(COUNTIF(MMULT(--(B2:E5="MOROCCO"),TRANSPOSE(COLUMN(B2:E5)^0)),">0"))

BTW any logical expression may be used instead of --(B2:E5="MOROCCO")

---

How it works

the first act is to have true and false array: ArrayFormula(B2:E5="MOROCCO")

The output is like this

true    false    false    false 
true    true     true     false
false   false    false    true
false   false    false    false

Then we need to convert it into 1 / 0 array. Simple math operation will do it:

ArrayFormula(--(B2:E5="MOROCCO"))

the output now is:

    1       0       0       0 
    1       1       1       0
    0       0       0       1
    0       0       0       0

Now we may add them by row: use mmult function. But first we need a column of 1 with the number of elements = number of columns in our array. To get it use formula: =ArrayFormula(TRANSPOSE(COLUMN(B2:E6)^0))

The result is:

    1    
    1    
    1    
    1 

And finally use mmult: =ArrayFormula(MMULT(--(B2:E6="MOROCCO"),TRANSPOSE(COLUMN(B2:E6)^0)))

The result is:

    1    
    3    
    1    
    0

I really don't know, why it works. just use this.

And final step is counting all that is > 0 = 3