【Excel 関数】SUMIFS・COUNTIFS・配列数式、似た関数はどう使い分けるか

この記事は約5分で読めます。

この記事で分かること

  • SUMIFS・COUNTIFS・SUMPRODUCT・FILTER系の違いと使い分け
  • 条件数・OR条件対応・可読性を基準にした比較
  • 迷ったときにどれを選べばいいかの考え方

条件集計の関数がいくつもあって、どれを使えばいいか毎回迷うという方に向けて書いています。

どんな場面で使うか

  • 複数条件での合計・件数を求めたいが、SUMIFSで足りるのかSUMPRODUCTが必要か分からない
  • 「AまたはB」のようなOR条件を集計に入れたい
  • 引き継いだファイルにSUMPRODUCTの複雑な数式があり、書き換えていいか判断したい

基本説明

条件集計に使える関数は複数あり、それぞれ得意・不得意があります。この記事では次の4つを比較します。

  • SUMIFS／COUNTIFS：複数条件のAND集計に使う、最も基本的な関数
  • SUMPRODUCT：配列同士の掛け算・条件判定を組み合わせられる、柔軟だが書き方が独特な関数
  • 配列数式（{}で囲む旧来の書き方）：SUMPRODUCT登場前によく使われた、OR条件や複雑な判定に対応する書き方
  • FILTER＋SUM（スピル関数の組み合わせ）：Microsoft 365以降で使える、抽出してから集計する新しい書き方

手順

比較表で全体像をつかむ

関数 複数条件（AND） OR条件 可読性 対応バージョン
SUMIFS・COUNTIFS 得意 不得意（工夫が必要） 高い 全バージョン
SUMPRODUCT 得意 対応可能（式が複雑になる） 中〜低い 全バージョン
配列数式（Ctrl+Shift+Enter） 得意 対応可能 低い（意図が読み取りにくい） 全バージョン（入力方法が特殊）
FILTER＋SUM 得意 得意（比較的読みやすく書ける） 高い Microsoft 365以降

選び方の考え方（フローチャートの文章版）

  1. 条件がすべてANDで済むか？ → はい：まずSUMIFS・COUNTIFSを検討する。多くの実務はこれで足ります。
  2. OR条件が必要、または集計以外の複雑な判定を組み合わせたいか？ → Microsoft 365が使えるならFILTER＋SUMを検討する。使えないならSUMPRODUCTを検討する。
  3. 引き継いだファイルに配列数式（{=...}）がある場合 → 動いているなら無理に書き換える必要はないが、修正が必要になったタイミングで、対応バージョンが許せばSUMIFSやFILTERベースに置き換えると保守しやすくなる。

サンプルコードまたは例

「エリアが東京、かつ商品カテゴリが電子機器」の売上合計を求める例で、書き方の違いを比較します。

SUMIFSで書く（AND条件・基本形）

=SUMIFS(売上金額範囲, エリア範囲, "東京", カテゴリ範囲, "電子機器")

条件が2つとも「かつ（AND）」で済む場合、これが最も読みやすく高速です。

「東京または大阪」を含めたい場合（OR条件）

SUMIFSは条件同士がANDでしか組めないため、OR条件を1つの数式で表すには工夫が必要です。

=SUMIFS(売上金額範囲, エリア範囲, "東京", カテゴリ範囲, "電子機器")
 + SUMIFS(売上金額範囲, エリア範囲, "大阪", カテゴリ範囲, "電子機器")

条件のパターンが少なければ、このようにSUMIFSを複数足し合わせる方法でも十分対応できます。

SUMPRODUCTで書く（OR条件を1本の数式にまとめる）

=SUMPRODUCT(((エリア範囲="東京")+(エリア範囲="大阪")) * (カテゴリ範囲="電子機器") * 売上金額範囲)

+でOR条件を、*でAND条件を表現しています。1本の数式で書けますが、+と*の意味を知らないと読み解くのが難しく、可読性は下がります。

FILTER＋SUMで書く（Microsoft 365以降）

=SUM(FILTER(売上金額範囲, ((エリア範囲="東京")+(エリア範囲="大阪")) * (カテゴリ範囲="電子機器"), 0))

条件式の書き方自体はSUMPRODUCTと似ていますが、「まず条件に合う行をFILTERで抜き出し、それをSUMする」という処理の流れが数式の見た目通りなので、SUMPRODUCTよりも意図が追いやすくなります。第3引数に0を指定しているのは、該当行が0件のときにFILTERが返す#CALC!エラーを防ぐためです（後述）。

よくあるエラー

  • SUMIFSでOR条件のつもりが集計結果が0件になる：SUMIFSの条件は常にANDとして評価されるため、「東京または大阪」を1つのSUMIFSの引数に並べて書いても意図通りになりません。SUMIFSを複数足し合わせるか、SUMPRODUCT・FILTERに切り替えます。
  • SUMPRODUCTで結果が想定より大きい・小さい：範囲の行数がずれている（片方が2:100、もう片方が2:101など）ことが原因で起きやすいです。すべての範囲の行数を揃えます。
  • FILTERの結果が#CALC!になる：条件に一致する行が1件もないときに発生します。SUMで包んでもこのエラーは消えません（SUMはエラー値をそのまま返します）。防ぐにはFILTERの第3引数（空の場合）を指定します。=SUM(FILTER(売上金額範囲, 条件, 0))のように書けば、該当行が0件でも0が返り、エラーになりません。

まとめ

条件集計は、まずSUMIFS・COUNTIFSで足りるかを確認し、OR条件や複雑な判定が必要になったときだけSUMPRODUCTやFILTERを検討する、という順番で選ぶと迷いません。可読性を優先するなら、対応バージョンが許す環境ではFILTER＋SUMが読みやすい選択肢です。

関連記事

タイトルとURLをコピーしました