Search code examples
google-sheetsgoogle-sheets-formulacountifgoogle-sheets-querygoogle-query-language

Count how many times appears text across columns

I'm looking for the formula to count how many times appears a color, for example, "red", from B2:D7 if A2:A7 is "B".

here is the example.

Solution 
  • =COUNTA(IFERROR(QUERY(A2:D, 
 "select A 
  where A = 'B' 
    and (B = 'RED' 
     or  C = 'RED'
     or  D = 'RED')", 0)))

    0

    =ARRAYFORMULA(COUNTA(IFERROR(QUERY(TRANSPOSE(TRIM(QUERY(TRANSPOSE(FILTER(
 IF(B2:D="RED", 1, ), A2:A="B")),,999^99))), "where Col1 is not null", 0))))

    0

    =ARRAYFORMULA(COUNT(IFERROR(FILTER(
 IF(B2:D="RED", 1, ), A2:A="B"))))

    9