英文:
Google Sheets - Multiple Columns Count if Cell Contains String... but only Count it once if string in same row more than once
问题
我看过这篇Stack Overflow文章，它接近我想要的东西。在那个示例中，一个人想要统计字符串在多列中的出现次数，但如果字符串在同一行中多次出现，则只计算一次。
那篇文章中的提问者有一组列在一起，B2:E5。我的情况是，有一些列我不想在其中搜索，只想在其中一些列中搜索。
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"
假设我想要统计在Col1、Col3和Col5中的"a"的实例。
=COUNTIF({Col1:Col1;Col3:Col3;Col5:Col5},"a")
返回4，因为它看到第二行有两个值。但我不想计算包含字符串的单元格数量，我想要计算行，只要其中任何单元格包含该字符串。
如何让这个公式只计算一次，而不管字符串在行中出现多少次？
英文:
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?
答案1
得分: 2
=sum(byrow(choosecols(A:E,1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))
英文:
You may try:
=sum(byrow(choosecols(A:E,1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论