Google Spreadsheet COUNTIFS で OR 条件を使いたい

Google Spreadsheet で条件に当てはまるレコード数をカウントしたい時に COUNTIF や COUNTIFS を使うが、複数条件ある時 COUNTIFS だとデフォルトが AND になるので OR でカウントしたいときの Tips

サンプルデータ

	A	B	C
1	販売商品コード	商品名	単価
2	A1012	ChatGTP入門	1670
3	A1018	React 入門コース	5800
4	A1020	プログラミング入門コース	2800
5	A1012	ChatGTP入門	1670
6	A1016	STUDIO デザインコース	2400
7	A1011	Spotifyコース	5800
8	A1018	React 入門コース	5800
9	A1019	AI 入門コース	2400
10	A1019	AI 入門コース	2400
11	A1012	ChatGTP入門	1670
12	A1020	プログラミング入門コース	2800

商品名が `ChatGTP入門` or `AI 入門コース` のレコード数を求める

1. 別々に COUNTIF で数を求め合算する

重複が発生しない条件なら、シンプルに条件ごとに COUNTIF で集計したものを足せば良い

= COUNTIF($B$2:$B$12, "ChatGTP入門") + COUNTIF($B$2:$B$12, "AI 入門コース")
// => 5

COUNTIF を足していく方法はシンプルで可読性も高いが、条件が増えると記述が面倒…

2. OR 条件を配列 `{}` で定義する方法

🙅 動作しない

= SUM(COUNTIFS($B$2:$B$12, {"ChatGTP入門", "AI 入門コース"}))
// => 3

Excel では上記のように COUNTIFS の条件を配列 {} 内に複数書けば OR 条件として動作するような記述があるが Google Spreadsheet では、最初の条件のレコードのみが返され、意図したとおりに動作しない

COUNTIFS function - Microsoft Support

COUNTIFS function and arrays - Microsoft Q&A

Use an array in the range argument of a countif? | Microsoft Community Hub

🙆 Google Spreadsheet では `ARRAYFORMULA` で囲えば OK

= ARRAYFORMULA(SUM(COUNTIF($B$2:$B12, {F2, F3})))
// => 5

3. MAP + LAMBDA を使う方法

MAP関数 MAP(配列1, [配列2, ...], LAMBDA)
この関数は、LAMBDA 関数を各値に適用して、指定した配列内の各値を新しい値にマッピングします。
cf. MAP function - Google Docs Editors Help
LAMBDA 関数 LAMBDA(名前, 数式)
一連の名前とそれらを使用する数式を含むカスタム関数を作成して返すことができます。 cf. LAMBDA function - Google Docs Editors Help

=COUNTIF(MAP($B$2:$B12, $B$2:$B12, 
  LAMBDA(a, b, OR(a="ChatGTP入門", b="AI 入門コース")))
  , TRUE)
// => 5

条件の分だけ範囲を作って渡す必要があるので、条件が多くなると少し見通しが悪くなりそうだと感じた

複数の OR 条件を AND したい場合

(A OR B OR C) AND (D OR E) のようなケース

商品名が React 入門コース or Spotifyコース or プログラミング入門コース or STUDIO デザインコース かつ単価が 5000 以上 or 2500以下 のレコード数を求める
(該当レコードは: 3 React 入門コース 5800, 6 STUDIO デザインコース, 2400, 7 Spotifyコース 5800,8 React 入門コース 5800 なので 4 になればよい

🙅 CONTIF を足すを方法では定義できない

全てが OR 条件になってしまうので AND 条件を表現できない

=(COUNTIF($B$2:B$12, "React 入門コース") + COUNTIF($B$2:B$12, "Spotifyコース") + COUNTIF($B$2:B$12, "プログラミング入門コース") + COUNTIF($B$2:B$12, "STUDIO デザインコース"))
 + (COUNTIF($C$2:C$12, ">=5000") + COUNTIF($C$2:C$12, "<=2500")) 
// => 15

🙅 配列 `{}` 定義・MAP + LAMBDA はエラーになる

配列 `{}` 定義

=ARRAYFORMULA(
  SUM(
    COUNTIFS(
      $B$2:$B$12, {"React 入門コース", "Spotifyコース", "プログラミング入門コース", "STUDIO デザインコース"},
      $C$2:$C$12, {">=5000", "<=2500"}
    )
  )
)
// => #VALUE

-> COUNTIFS の配列引数のサイズが異なります。

MAP + LAMBDA

=COUNTIFS(
  MAP($B$2:$B$12, $B$2:$B$12, $B$2:$B$12, $B$2:$B$12,
    LAMBDA(a, b, c, d OR(a="React 入門コース", b="Spotifyコース" c="プログラミング入門コース", d="STUDIO デザインコース"))), TRUE,
  MAP($C$2:$C$12, $C$2:$C$12,
    LAMBDA(a, b OR(a>=5000, b<=2500))), TRUE
)
// => #ERROR!

-> 数式の解析エラーです。

🙆 素直に query を使う

query を使い結果を count で出せば良い

=QUERY(A2:C12, 
  "SELECT count(A) 
   WHERE
     (B = 'React 入門コース' OR B = 'Spotifyコース' OR B = 'プログラミング入門コース' OR B = 'STUDIO デザインコース') 
     AND (C >= 5000 OR C <= 2500)
   LABEL count(A) ''"
)
// => 4

素直に query で書くほうが SQL が読めるなら見通しも良くて良い

まとめ

OR 条件が1つだけなら ARRAYFORMULA(SUM(COUNTIF(範囲, {条件1, 条件2, 条件3, ...}))) か Query を使うのが良さそう
複数の OR 条件を AND するような複雑な条件の場合は、素直に query を使い結果を count(A) のような形で出すのが良い

[参考]