I’ve seen this SO article that comes close to what I want. In that example a person wanted to count the occurrence of a string across multiple columns – but only once if the string appeared in the same row more than once.

The OP in that article had a block of columns all together, B2:E5. My situation is that there are some columns that I don’t want searched inbetween the ones that I do want to search.

Col1, Col2, Col3, Col4, Col5
"a"   "a"   "b"   "c"   "d"
"a"   "b"   "a"   "c"   "d"
"b"   "a"   "c"   "d"   "d"
"b"   "c"   "a"   "d"   "c"

Lets say I want to count the instances of "a" in Col1, Col3, and Col5.

=COUNTIF({Col1:Col1;Col3:Col3;Col5:Col5},"a")
returns 4 because it sees two values in the second row. But I don’t want to count how many cells contain a string, I want to count the row if any of the cells have that string.

How do I get the formula to count only once regardless of how many times the string appears in the row?

>Solution :

You may try:

=sum(byrow(choosecols(A:E,1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))