英文:
Google Sheets - Sum row if row contains at least one instance of string in column, AND column next to sorted columns contain another string
问题
这个问题是我之前提出的问题的延伸。在那个情况下，如果一行中的一个或多个列包含字符串"a"，则=sum(byrow(choosecols(A:E,1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))会计算一行。无论该行中字符串出现的次数多少，都会计算一次。
现在我想要计算一行，如果选定的列包含一个字符串，并且紧挨着选定的列的列包含一个不同的字符串。
col1 col2 col3 col4 col5 col6
r1 "a" "b" "c" "d" "e" "a"
r2 "a" "c" "a" "d" "e" "c"
r3 "b" "a" "c" "a" "d" "c"
r4 "a" "c" "a" "c" "e" "d"
r5 "e" "a" "c" "d" "a" "e"
我只想计算一行，如果col1、col3或col5包含"a"，并且与包含"a"的单元格相邻的列包含"c"，也就是说，如果col1包含"a"，那么col2必须包含"c"。如果col3包含"a"，那么col4必须包含"c"。
因此，使用上面的表格作为示例，我想要一个公式返回2，因为只有r2和r4满足条件，即使r4满足条件两次，也应该只计算一次。
英文:
This question is an extension of another one I asked. In that case =sum(byrow(choosecols(A:E,1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))
counted a row if one or more columns in that row contained "a" string. The row was counted once regardless if the row had multiple instances of the string appearing.
Now I want to count a row, if selected columns contain a string, and the column next to the selected column contain a different string.
col1 col2 col3 col4 col5 col6
r1 "a" "b" "c" "d" "e" "a"
r2 "a" "c" "a" "d" "e" "c"
r3 "b" "a" "c" "a" "d" "c"
r4 "a" "c" "a" "c" "e" "d"
r5 "e" "a" "c" "d" "a" "e"
I only want to count a row, if col1, col3 or col5 contain "a" AND the column next to the cell with "a" contains "c" ie. if col1 contains "a" then col2 needs to contain the "c". If col3 contains the "a" then col4 must contain the "c"
So using the above table as an example, I want a formula that returns a count of 2, as only r2 and r4 match the conditions, and even though r4 matches the conditions twice it should only be counted once.
答案1
得分: 1
添加一个扩展到之前的问题公式：
= sum（按行（选择列（索引（如果（（A：E=“a”）*（B：F=“c”），“a”，）），1,3,5），lambda（Σ，ifna（xmatch（“a”，Σ）^0）））
英文:
Adding an extension to the earlier question formula:
=sum(byrow(choosecols(index(if((A:E="a")*(B:F="c"),"a",)),1,3,5),lambda(Σ,ifna(xmatch("a",Σ)^0))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论