SUMIF/SUMIFS関数がうまくいかない原因と対処法5選！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

SUMIF／SUMIFS関数が思ったとおりに集計できないときは、数式の書き方ではなく「データ側のクセ」が原因であることがほとんどです。
今回は
・支店別売上表で、SUMIFとSUMIFSを正しく使い分けて集計する方法
をゴールにします。
実際に「うまくいかない5つの場面」を再現し、それぞれの原因と対処法を表で確認できるようにしました。

なお、原因を1つずつつぶす考え方に加えて、VBAで数式を一括設定する方法と、うまくいかないときのトラブルシュートもあわせてご紹介します。
早速実装して試してみましょう！

上の画像が完成形です。F24に東京の合計、F25に東京×ノートPCの合計が入り、条件を変えれば数字がすぐ切り替わります。

STEP1. サンプルの売上表を用意する

まずは集計したい支店別の売上表を用意します。
1行目が見出し、2行目から21行目までがデータです。
金額列（F列）は手入力ではなく =D2*E2 の数式にしておきましょう。
数式にしておくと、数量や単価を変えても金額が自動で計算されるのでおすすめです。

STEP2. まずは正しい数式を入れてみる

表の下に集計エリアを作ります。
F24セルに =SUMIF(A2:A21,”東京”,F2:F21) を入力します。
続けてF25セルに =SUMIFS(F2:F21,A2:A21,”東京”,C2:C21,”ノートPC”) を入力します。
東京の合計は 1,647,800、東京×ノートPCの合計は 1,024,000 になりました。
この2つが基準になる正解値です。

数式はどのセルに入れる？

セルを選んで、数式バーに直接入力するだけです。
実際の入力画面がこちらです。
入力中は引数のヒントが表示されるので、順番を確認しながら進められます。
カンタンですね。

SUMIFSは引数の順番に注意

SUMIFSは 合計範囲が最初 に来ます。
SUMIFと逆なので、ここで迷う方がとても多いです。
条件は「条件範囲, 条件」のセットで、後ろに増やしていきます。

うまくいかない原因と対処法5選

ここからは、実際につまずきやすい5つの場面を順に見ていきます。
どれも数式そのものは合っているのに、結果が合わないケースです。
原因と対処をセットで覚えてしまいましょう。

原因1. 条件範囲と合計範囲の行数・向きがちがう

SUMIF関数は、条件範囲と合計範囲の行数・向きをそろえる必要があります。
合計範囲を1行ずらしてしまうと、Excelは範囲の左上を起点に伸ばすため、意図せず1行分ずれた集計になります。
下の画像では、F3:F22と1行ずらした例が 1,054,600 になり、正しい 1,647,800 と差が出ています。

原因2. 検索値の全角半角や余分な空白で一致しない

見た目は同じ「東京」でも、前後に空白があると別の文字列と判定されます。
下の画像では、検索値に余分な空白を付けた例が 0 になっています。
キーボードから入力した検索値に、知らないうちに半角スペースが入っていることがよくあります。

原因3. 数値条件の指定ミス

「50,000以上」のような条件は、比較演算子ごと文字列として指定します。
正しくは “>=50000” のように、不等号もダブルクォーテーションで囲みます。
不等号をそのまま書いてしまうと #NAME? エラーになります。
下の画像では、正しい書き方の結果が 3,142,800 になっています。

原因4. ワイルドカードの扱い

「〜を含む」で検索したいときは、前後に * を付けます。
「PCを含む商品」なら “*PC*” です。
一方で、* や ? という記号そのものを検索したいときは、前に ~ を付けて打ち消します。
なお、数値や日付にはワイルドカードは効きません。
下の画像では「*PC*」の合計が 1,920,000 になっています。

原因5. 金額が文字列になっていて合計が0になる

金額が 文字列として入力されていると、SUMIFやSUMはそのセルを無視して0になります。
文字列の数値は左寄せになり、セルの左上に緑の三角が出ることが多いです。
下の画像では、文字列をそのまま合計した例が 0、VALUEで数値化した例が 384000 になっています。

SUMIFとSUMIFSの使い分け

条件が1つならSUMIF、2つ以上ならSUMIFSを使います。
引数の順番が逆なので、そこだけは必ず確認しましょう。

VBAでSUMIF・SUMIFSを一括設定する

毎回セルに数式を書くのが手間なときは、VBAでまとめて設定するのもおすすめです。
ボタン1つで集計エリアを作れます。

Sub SetSumIf()
    Dim ws As Worksheet
    Set ws = Worksheets("売上データ")
    ws.Range("F24").Formula = "=SUMIF(A2:A21,""東京"",F2:F21)"
    ws.Range("F25").Formula = "=SUMIFS(F2:F21,A2:A21,""東京"",C2:C21,""ノートPC"")"
    ws.Range("A24").Value = "東京の合計（SUMIF）"
    ws.Range("A25").Value = "東京×ノートPC（SUMIFS）"
End Sub

タカヒロ

SUMIFとSUMIFSは引数の順番が逆なので、そこだけは必ず確かめましょう。
迷ったら「合計範囲は最初か最後か」を思い出すと整理しやすいですよ。

うまくいかないときのトラブルシュート

それでも合わないときは、次の表で症状を確認してみてください。

関数の説明

最後に、今回使う2つの関数の基本を確認しておきましょう。

SUMIF関数（サムイフ）

=SUMIF(A2:A21, "東京", F2:F21)

SUMIFS関数（サムイフズ）

=SUMIFS(F2:F21, A2:A21, "東京", C2:C21, "ノートPC")

完成

条件を変えるだけで、集計結果がすぐに切り替わるようになりました。
F24には東京の合計 1,647,800、F25には東京×ノートPCの合計 1,024,000 が入っています。
5つの原因をつぶしたことで、同じ数式でも安心して使えるようになりました。
思ったとおりに動きましたか？

さいごに

いかがでしょうか。

今回は、

についてまとめました。

条件範囲と合計範囲をそろえること、数値条件を文字列で指定すること。
この2つを押さえておくと、多くのつまずきは防げます。

まずは今回のサンプル表で、ぜひ一度試してみてください。
原因が1つずつ消えていく感覚がつかめるはずですよ。

また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。