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.
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))))