COUNTIF over multiple non contiguous ranges and with one condition in Google sheets-CodePudding

There's a google sheet with content like this:

    | A           |  B       |
------------------------------
 1  | Banana      | Brazil   |
 2  | Banana      | Uruguay  |
 3  | 2022-02-28 by flight   |    // Cell spans over two columns
 4  | Pineapple   | Paraguay |
 5  | Banana      | Brazil   |
 6  | 2022-03-08 by Ship     |    // Cell spans over two columns
 7  | Coconut     | Uruguay  |
 8  | Banana      | Peru     |

I need the amount of non "Brazil" entries of certain lines (excluding the combined ones). It should be st like this =COUNTIF(INDIRECT({B1:B2;B3:B5;B7:B9});"<>Brazil") which doesn't work. Is there an applicable way without using a series of COUNTIFS in which the condition has to be set for every single range?

If this is not possible that way then QUERY might be helpful (see

update:

=COUNTIFS(B:B; "<>Brazil"; B:B; "<>")

CodePudding user response：

This skips row 3 and 6 using a filter:

=LAMBDA(ar,len,COUNTIF(FILTER(ar,SWITCH(SEQUENCE(len),3,0,6,0,1));"<>Brazil"))(B1:B9,ROWS(B1:B9))

Or with a named function:

SKIP(arr,rows_to_skip):

=FILTER(arr,
  MAP(SEQUENCE(ROWS(arr)),
    LAMBDA(n, AND(MAP(rows_to_skip,LAMBDA(s, s<>n))))
  )
)

Then,

=COUNTIF(SKIP(B1:B9,{3,4,6}),"<>Brazil")

Skips row 3, 4 and 6 in the array B1:B9