Search code examples
google-sheetsgoogle-sheets-formulaformulaspreadsheet

Google Sheets - Sum row if row contains at least one instance of string in column, AND column next to sorted columns contain another string


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.


Solution

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

    enter image description here