英文:
Google Sheets - Count Row if Values in Adjoining Cells Match Two Strings AND Another Value Pair is not Present in the same Row
问题
与我以前的[SO文章][1]类似，我想要计算一行，如果两个相邻的单元格包含指定的字符串，那么我想要计数这一行。如果行包含另一对指定的字符串，那么无论是否符合第一个条件，我都不想计数这一行。
这是一个示例...在下面的表格中，我首先想要检查col1、col4和col7是否有任何单元格包含string1。如果它们中的任何一个包含，我想要检查紧邻它的单元格，看它是否包含string2。如果包含，则计数为1...
...除非在同一行中col1、col4和col7也包含string1，但相邻的单元格包含string3。
另外，如果正确的单元格中有多个string1和string2的实例，仍然只计为1。
string1 = "a"
string2 = "b"
string3 = "c"
col1 col2 col3 col4 col5 col6 col7 col8 col9
row1 "a" "b" "c" "d" "e" "x" "y" "z" "o"
row2 "c" "d" "a" "b" "a" "c" "z" "o" "y"
row3 "a" "y" "a" "a" "b" "o" "a" "c" "x"
row4 "a" "b" "x" "d" "e" "z" "a" "b" "c"
因此，对于这个示例表格，我希望最终结果是2，因为row1在col1中有string1，col2中有string2。行4在正确的列中有string1和string2两次，但仍然只计为一次。行2不计算，因为string1和string2在错误的列中。行3也不计，因为尽管string1和string2在正确的列中，但也有一对string1和string3在正确的单元格中。
编辑
这是实际电子表格的链接。如果您查看“results”工作表，E7单元格。在这种情况下，公式应返回0，因为“Firey War Axe”被“dumped”两次，但仍然保留。请参阅“data”工作表中的示例，第44行和第56行。这两行不应计数。
英文:
Similar to my [previous SO article][1], I want to count a row if two adjoining cells contain specified strings. If the row contains another specified pair of strings then I don't want to count the row regardless if it meets the first criteria.
Here's an example... in the below table, I first want to check col1, col4, and col7 if any of the cells contain string1. If any of them do, I want to check the cell immediately adjacent to it, to see if it contains string 2. If it does then it counts as 1...
...unless in the same row either col1, col4, and col7 also contain string1, but the adjacent cell contains string3.
Additionally if there are multiple instances of string1 and string2 in the correct cells, its still only counts as 1.
string1 = "a"
string2 = "b"
string3 = "c"
col1 col2 col3 col4 col5 col6 col7 col8 col9
row1 "a" "b" "c" "d" "e" "x" "y" "z" "o"
row2 "c" "d" "a" "b" "a" "c" "z" "o" "y"
row3 "a" "y" "a" "a" "b" "o" "a" "c" "x"
row4 "a" "b" "x" "d" "e" "z" "a" "b" "c"
So with this example table I would want a final result of 2 as row1 has string1 in col1 and, and col2 has string2 in it. Row 4 has string1 and string2 in the right columns twice, but still only counts once. Row 2 doesn't count because string1 and string2 are in the wrong columns. Row 3 doesn't count because despite string1 and string2 being in the right columns, there is also a pairing of string1 and string3 in the right cells.
EDIT
Here is the link to the actual spreadsheet. If you look at the 'results' sheet, E7 cell. The formula should return 0 in this case, as there are two times when 'Firey War Axe' is "dumped" but also kept. See row 44 and row 56 in the 'data' sheet for the examples. These two rows should not be counted.
答案1
得分: 1
=countif(byrow(choosecols(index(ifs((Data!E:N=A7)(Data!F:O="Dumped"),A7,(Data!E:N=A7)(Data!F:O="Kept"),"Dumped",1)),1,4,7,10),lambda(Σ,
ifna(xmatch(A7,Σ)^0)+let(Λ,ifna(xmatch("Dumped",Σ)),if(Λ=0,,max(2,Λ))))),1)
英文:
Updated answer:
=countif(byrow(choosecols(index(ifs((Data!E:N=A7)*(Data!F:O="Dumped"),A7,(Data!E:N=A7)*(Data!F:O="Kept"),"Dumped",1,)),1,4,7,10),lambda(Σ,
ifna(xmatch(A7,Σ)^0)+let(Λ,ifna(xmatch("Dumped",Σ)),if(Λ=0,,max(2,Λ))))),1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论