Excel SUMIFS関数が合計されない原因と対処法を試した話

ExcelのSUMIFS関数で集計したのに、結果が0になって合計されないことはありませんか。明細を電卓で足した合計と、SUMIFSの答えが合わないこともあります。しかもエラーが出ないので、原因も対処の仕方も見当がつきません。そこで今回、2〜4行だけの小さな表を作りました。そして、0になりそうな原因をExcelで一つずつ再現して試しました。実際に試すと、関数が壊れているわけではありませんでした。原因の多くは、引数の順番と、条件に渡している値の中身にありました。

試したのは、Windows版のMicrosoft 365のExcelです。記事の画面は、そのExcelで実際に計算させた結果を撮ったものです。表はどれも数行だけなので、同じ表を作れば手元でもすぐに確かめられます。この記事では、試した結果をExcelの画面つきで共有します。数式を作り直す前に、上から順に確かめてみてください。このあと、引数の順番、空白と全角半角、日付、文字列の数字、空白条件とワイルドカードの5つを順に見ていきます。

本記事にはプロモーション（アフィリエイトリンク）を含みます。

SUMIFSで合計されず0になって困った話

最初につまずいたのは、SUMIFSがエラーを出さずに0を返したことです。エラー表示があれば、どこが悪いのか探しやすいはずです。ところが0という答えが出るので、数式が正しいように見えてしまいます。

SUMIFを書き慣れた順番のまま、SUMIFSを書いてみました。するとエラーは出ずに、0だけが返ってきました。同じ表でSUMIFを使うと、東京の売上はきちんと100と出ます。つまり、データではなく書き方の違いだけで0になっていたのです。そこで、原因を切り分けるために小さな表を作りました。地域と売上の2列だけの表です。行が少なければ、正しい答えを先に暗算で出せます。たとえば東京の売上が100なら、SUMIFSも100を返すはずです。答えがずれたら、その書き方かデータに原因があると分かります。この方法で、0になるパターンを順番に確かめていきました。試した計算は、全部で24通りです。

表は新しいブックに作り、元の集計表には手を付けませんでした。元の表を直しながら試すと、どこを変えたのか分からなくなるからです。また、1回に変えるのは条件か書き方のどちらか1つだけにしました。こうしておくと、結果が変わった理由を1つに絞れます。

まずSUMIFと逆になる引数の順番を確認する

SUMIFS関数でいちばん起きやすいのは、引数の順番の間違いです。とくにSUMIF関数に慣れている人ほど、無意識にやってしまいます。SUMIFは「範囲、検索条件、合計範囲」の順番で書きます。一方でSUMIFSは「合計対象範囲、条件範囲1、条件1」の順番です。つまり、合計したい列が先頭に来ます。

Microsoftの公式ページにも、SUMIFとSUMIFSでは引数の指定順序が異なると書かれています。実際にSUMIFの順番でSUMIFSを書くと、画面のとおり結果は0でした。正しい順番に直すと、SUMIFと同じ100が出ます。困るのは、間違えてもエラーにならない点です。合計範囲と条件範囲が入れ替わると、条件に合う行が見つからなくなります。その結果、Excelは0を返して終わってしまいます。

まずは数式の先頭の範囲を見てください。数式バーで数式をクリックすると、参照している範囲が色の枠で表に表示されます。そこが金額や個数の列なら大丈夫です。もし地域名や商品名の列になっていたら、順番を入れ替えます。なお、範囲の行数がずれているときは、0ではなくエラーになります。こちらは別の記事で、範囲のサイズをそろえる手順をまとめました。

条件セルに余計な空白や全角半角の違いがないか見る

SUMIFSが合計されないとき、次に疑ったのは条件として渡している文字そのものです。見た目は同じ「東京」でも、末尾に半角スペースが入った「東京 」は別の文字として扱われます。SUMIFSは一致した行だけを合計するので、その行は合計から漏れてしまいます。

地域の列に「東京」「東京 」「大阪」の3行を用意して試しました。LEN関数で文字数を見ると、見た目は同じ東京が2文字と3文字に分かれました。「東京」で合計すると、300になるはずが100でした。そこでTRIMで前後の空白を消してから比べると、300に戻りました。

LEN関数の結果が見た目の文字数より多ければ、どこかに見えない空白があります。コピーして貼り付けたデータには、元の空白もそのまま入ってきます。空白は、TRIM関数で消すのが手軽です。TRIMは前後の空白を消し、間に続く空白は1つにそろえます。試すと、末尾の全角スペースもTRIMで消えました。置換でまとめて消す方法もあります。手順は、検索する文字列に半角スペースを1つ入れ、置換後の文字列を空にして実行するだけです。ただし置換は、氏名の間の空白のような必要な空白まで消します。範囲を選んでから実行するのが安全です。

全角と半角は英字とカナで一致しなかった

続いて、全角と半角の違いを試しました。大文字と小文字は区別されず、「ABC」で探すと「abc」の行も合計されました。ただし、全角の「ＡＢＣ」は一致しませんでした。半角カナの「ｶﾞｽ」も、全角の「ガス」とは別の文字として扱われます。一方で、数字だけの文字列は全角の「１２３」でも「123」に一致しました。表記をそろえるなら、ASC関数で全角の英数字やカナを半角に変換できます。試すと、「ＡＢＣ」は「ABC」に、「ガス」は「ｶﾞｽ」に変わりました。逆に全角へそろえたいときは、JIS関数を使います。JISでは「ABC」が「ＡＢＣ」に、「ｶﾞｽ」が「ガス」

に変わりました。どちらにそろえるかは、条件に書く文字と合わせるのがポイントです。条件を「ＡＢＣ」と全角で書くと、今度は全角の行だけが合計されました。直すときは、元の列はそのままにして、隣の列にASCの結果を作ると安心です。元の値と見比べながら、条件範囲をASCの列に差し替えられます。

日付条件は表示ではなく中身の値を確認する

日付を条件にするときは、書き方と中身の2か所でつまずきました。まずは書き方です。条件を数式に直接書くなら、「”>=2026/9/1″」で正しく合計されました。ところが、基準日をセルE2に入れて「”>=E2″」と書くと、結果は0でした。これでは、E2の日付ではなく「E2」という文字と比べてしまいます。

「”>=E2″」を「”>=”&E2」に書き換えると、0だった結果が300になりました。演算子をダブルクォーテーションで囲み、アンパサンドでセルとつなぐ書き方です。この書き方は、Microsoftの公式ページの例にも載っています。

次に中身です。見た目が「2026/09/20」でも、文字列として入っている日付は条件に引っかかりません。試した表では、文字列の日付の1行（売上400）が集計から漏れました。本来は700になるはずの合計が、300になったのです。文字列の日付は左寄せで表示されるので、並べると見分けがつきます。画面の「中身」の列は、ISTEXT関数で文字列か日付かを判定したものです。DATEVALUE関数で日付の値に直してから集計すると、合計は700に戻りました。また、基準日をセルに入れて&でつないでおくと、日付を打ち替えるだけで集計し直せます。

合計範囲に数値に見える文字列が混ざっていないか確認する

意外と気づきにくいのが、合計する列に混ざった「数字に見える文字列」です。たとえば、売上の列に「’200」のように入力された値があるとします。アポストロフィ付きの200は、見た目は数字でも中身は文字列です。SUMIFSはその行をエラーにせず、黙って合計から外します。

試した表では、100と200と300で合計600になるはずが、SUMIFSでは400でした。厄介なのは、エラーが出ないので数式のほうを疑ってしまう点です。ISTEXT関数で確かめると、200のセルだけがTRUEになりました。また、セルの左上に緑の三角が付いていれば、文字列の数字のサインです。直すときは、緑の三角のメニューから「数値に変換する」を選びます。列ごとまとめて直すなら、［データ］タブの［区切り位置］も使えます。列を選んで区切り位置を開き、そのまま［完了］を押すと、文字列の数字が数値に変わりました。

または、VALUE関数で数値に直した列を別に作り、その列を合計範囲にする方法もあります。ちなみに、画面の600は、文字の200も数値として掛け算で足す式で出した値です。条件範囲の側に文字列の数字があっても、数値の条件で一致しました。問題になったのは、合計する列のほうだけです。

空白条件とワイルドカード条件を分けて試す

空白を条件にするときは、書き方によって合計される行が変わります。条件を「””」にすると、本当の空白セルと、数式の結果が空文字のセルの両方が合計されました。「”=”」にすると、本当の空白セルだけでした。さらに「”<>”」にすると、数式の空文字のセルも「空白以外」として合計されました。見た目が同じ空欄でも、中身が違うと結果が変わるのです。空欄に見えるセルが数式かどうかは、セルを選んで数式バーを見ると分かります。「空白以外」の合計が多すぎるときは、空文字を返す数式のセルを疑ってみてください。

ISBLANK関数でも、数式の空文字のセルはFALSEになりました。一方でCOUNTBLANK関数は空文字のセルも空白として数えるので、4行のうち2件です。

試した表では、同じ4行でも条件の書き方だけで合計が600、200、1300と変わりました。空欄をどう扱うかを先に決めてから条件を書くと、迷いが減ります。数式で空文字を返すセルは、IF関数で「該当がなければ空欄にする」表でよく見かける形です。空文字も除いて合計したいときは、隣の列にLEN関数で文字数を出す方法が使えます。その列を条件範囲にして「”>0″」を指定すると、東京と大阪の900だけが合計されました。

ワイルドカードはアスタリスクとチルダで使い分ける

今度は、ワイルドカードを試しました。アスタリスクは任意の文字列、クエスチョンマークは任意の1文字を表します。「東京*」なら東京で始まる行、「*東京*」なら東京を含む行が合計されました。ただし、アスタリスクそのものを探したいときは、前にチルダを付けて「東京~*」と書きます。すると、「東京*」という文字の行だけが合計されました。クエスチョンマークは1文字分なので、「東京??」なら東京本社や東京支店のような4文字の行だけが対象でした。一方で「東京?」にすると、3文字の「東京*」の行だけが合計されました。

条件をセルに入れて部分一致にしたいときは、「”*”&E1&”*”」のように&でつなぎます。E1に「東京」と入れ、「”*”&E1」にすると、東京で終わる新東京の400だけが合計されました。なお、部分一致は思わぬ行まで拾うことがあります。「*東京*」では、新東京の行も合計に入りました。同じ条件でCOUNTIFS関数を使うと、何行が対象になったかを件数で確かめられます。試すと、「東京*」は3件、「*東京*」は4件、「東京~*」は1件でした。複数の条件を重ねても合わないときは、条件を一つずつ外してみてください。どの条件で合計が崩れるのかが分かります。

SUMIFSが合計されないときの確認順まとめ

試して分かった確認の順番を、最後にまとめます。最初に見るのは、数式の先頭が合計したい列になっているかです。次に、条件の文字に前後の空白や全角半角の違いがないかを確かめます。

さらに、日付の条件が「”>=”&セル」のように&でつながっているかも大事でした。日付の列に、文字列の日付が混ざっていないかも見ておきます。そのうえで、合計する列に文字列の数字がないかをISTEXT関数で探します。最後は、空白の条件やワイルドカードの書き方の見直しです。元の表が大きいときは、合わない行だけを新しいシートに3〜4行コピーして試すと早いです。とくに0が返るときは、引数の順番と日付の&を最初に疑うのが近道でした。数式を全部作り直す前に、この順番で一つずつ確かめてみてください。

関連リンクとチェックリスト

学習サービスとアンケート