Google Sheets - Multiple Columns Count if Cell Contains String... but only Count it once if string in same row more than once

Question

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?

3... so the second row only gets counted once, despite have more than one instance of "a" in the filtered columns — Mr. J, May 24 '23 at 09:20

score 2 · Accepted Answer · answered May 24 '23 at 09:22

2

You may try:

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

answered May 24 '23 at 09:22

rockinfreakshow

15,077
3
12
19

Google Sheets - Multiple Columns Count if Cell Contains String... but only Count it once if string in same row more than once

1 Answers1

Linked