Excel約5分で読めます

ExcelのSUMIFSで複数条件が効かない|範囲の大きさをそろえる

ExcelのSUMIFSで複数条件が効かないを表したイメージ
記事の内容を表したイメージ(AI生成)

SUMIFSの数式を入れたのに結果が0になる、あるいは#VALUE!エラーが出て先に進めない。そういうときの原因として最も多いのが「合計範囲と条件範囲の行数・列数が一致していない」問題です。

SUMIFSは、合計範囲と各条件範囲がまったく同じ大きさでなければ正しく動きません。どちらかの範囲を広く取りすぎたり、ある条件範囲だけ1行少なかったりすると、Excelは「範囲が合っていない」と判断して0や#VALUE!を返します。これはSUMIFにはない、SUMIFSに特有のルールです。

この記事では、範囲の大きさのズレがなぜ起きるのかを整理し、確認と修正の手順を示します。同時に、大きさは合っているはずなのに条件が効かない場合に疑う副次的な原因も取り上げます。


なぜ「範囲の大きさ」が問題になるのか

SUMIFSの構文を確認します。

=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)

Excelはこの数式を実行するとき、合計範囲の各セルに対して「条件範囲1の対応するセルが条件1を満たすか、かつ条件範囲2の対応するセルが条件2を満たすか」を1行ずつチェックします。この「対応するセル」を特定するために、すべての範囲が同じ行数・列数でなければならない、というルールがあります。

たとえば合計範囲がD2:D51(50行)なのに条件範囲1がA2:A52(51行)だと、Excelは対応関係を作れずに#VALUE!を返します。0が返る場合は大きさは一致しているものの条件がどこにも当てはまっていない状態ですが、範囲の一部がずれていると条件判定が見当違いの行に対して行われ、結果的に0になることもあります。

画面の表示は版や設定により異なります。以下の操作名はExcel一般の呼び名で記載しています。


範囲のズレを確認する手順

作業の前に、ファイルを別名でコピーして元のデータを保全してください。

1. 数式バーで範囲を目で読む

問題の数式が入ったセルを選択し、数式バーで各範囲の行番号を読み比べます。

架空の例です。

=SUMIFS(D2:D51, A2:A51, "東京", B2:B52, "4月")
  • 合計範囲:D2:D51(50行)
  • 条件範囲1:A2:A51(50行)← 合っている
  • 条件範囲2:B2:B52(51行)← 1行多い

この例では条件範囲2だけ1行多く、#VALUE!の原因になります。

2. キーボードショートカットで範囲を視覚確認する

数式バー内の各範囲アドレスをクリックすると、該当セルがシート上で色つきの枠で示されます。複数の枠を目で見比べて、始まりと終わりの行が同じかどうかを確認します。大量のデータで行番号の読み違えが起きやすい場面では、この方法が有効です。

3. ROWS関数で行数を数値として比較する

空いているセルに次の数式を入れて、各範囲の行数を数値で出してから比べます。

=ROWS(D2:D51)    → 50
=ROWS(A2:A51)    → 50
=ROWS(B2:B52)    → 51

この方法は、行数が100を超えるときに目視よりも確実です。列方向に範囲を取っている場合はCOLUMNS関数を同様に使います。


修正の考え方と典型パターン

パターン1:貼り付けやオートフィルで範囲の終端がずれた

別の列を後から条件に追加したとき、もとの数式からコピーして範囲アドレスだけ書き換えると、終端行を修正し忘れることがよくあります。このときは全範囲の終端行を合計範囲に合わせて統一します。

修正前の例(架空):

=SUMIFS(D2:D100, A2:A100, F2, B2:B99, G2)

修正後:

=SUMIFS(D2:D100, A2:A100, F2, B2:B100, G2)

パターン2:テーブル機能を使わずに行を追加した

データを下に追加したとき、一部の範囲だけ手動で広げ、ほかの範囲は広げ忘れることがあります。テーブル機能(Excelの「テーブルとして書式設定」)を使うと、データ範囲の拡張に合わせて数式内の参照が自動的に広がります。ただしテーブル機能の使い方は本記事の範囲外ですので、公式のヘルプで確認してください。

パターン3:絶対参照と相対参照を混在させた

数式をほかのセルにコピーしたとき、条件範囲の一部が相対参照($なし)になっていると、コピー先でアドレスがずれます。複数セルにコピーして使う数式では、合計範囲・条件範囲にはすべて絶対参照を付けることを原則にします。

=SUMIFS($D$2:$D$100, $A$2:$A$100, F2, $B$2:$B$100, G2)

条件値(F2やG2)は行ごとに変わるので相対参照のままにします。


範囲の大きさは合っているのに0が返る場合

範囲のズレを修正しても0が返るときは、別の原因を疑います。

スペースや改行の混入:セルに「東京」と入力したつもりでも、前後に全角・半角スペースや改行が混じっていると条件が一致しません。確認するにはセルを選択して数式バーで末尾を目視するか、LEN関数で文字数を調べます。

=LEN(A2)    ← 「東京」なら2が返るはず。3以上なら余分な文字がある

数値と文字列の混在:見た目が同じでも、片方が数値、もう片方が文字列として入力されている場合に条件が一致しません。条件範囲のセルの書式と条件値の型を確認します。数値として入っているかどうかはISNUMBER関数で確認できます。

条件の書き方:比較演算子を含む条件は文字列として書く必要があります。たとえば「100以上」を指定するときは">=100"と書きます。数値だけの場合は100と書いてもよく、文字列で一致を取るときは"東京"のようにします。

迷いやすい例外:条件に空白セルを指定したいときは""(空文字列)を使いますが、セルが数式で空文字列を返している場合(="")と、本当に何も入力されていない場合では挙動が異なることがあります。この区別は数式を入れたセルと未入力のセルが混在するリストで発生しやすいです。


大きな表で範囲ズレを防ぐ設計

毎回手作業で範囲を確認するのは手間がかかります。最初から範囲を列全体(A:Aなど)にする方法もありますが、同じ列に見出し行が含まれる場合は見出しも集計対象に入ることがあるため注意が必要です。見出し行を除くには行番号を明示する方法のほうが安全です。

範囲がよく変わる表では、データをテーブルに変換しておくとSUMIFSの参照が自動で広がります。テーブル参照の書き方は通常の範囲アドレスと異なりますので、導入前に公式のヘルプで書き方を確認してください。


次の行動

まず問題の数式を選択して数式バーを開き、各範囲の始端と終端の行番号を読み比べてください。行数が多くて目視しにくい場合は、空きセルにROWS関数を使って各範囲の行数を数値で並べると短時間で差を見つけられます。範囲を修正したあとも0が返る場合は、条件値のスペース混入や数値・文字列の型違いをLEN・ISNUMBERで確認してください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ