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

Robbit 0
Viewed 45

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 Google Sheets Query Non-Contiguous Range). Then how to obtain the number of the results?

Thank You very much.

merge countif count google-sheets arrays
2 Answers

perhaps you overthinking it:

=COUNTIF(B:B; "Brazil")

enter image description here

update:

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

enter image description here

player00

You don't even need INDIRECT: Just =COUNTIF({B1:B2;B3:B5;B7:B9},"<>Brazil") will work.

To create skips, use FILTER+SEQUENCE.

For eg, This skips row 3 and 6:

=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

TheMaster0
Related