SUMIFS関数がなぜうまくいかない？#VALUE!エラーの原因

ここでは、ExcelのSUMIFS関数がうまくいかず、#VALUE!エラーになってしまう原因2つと対処法を解説します。

特に気づきにくい原因もありますので、SUMIFS関数を使いこなせるようになるために押さえておいた方がいいと思います。

【目次】

１．閉じた他のファイルを参照している
２．検索条件毎の行範囲が違う

本記事はアフィリエイト広告を含みます

パソコンはメーカー直販サイトがお得！
おすすめメーカーの解説はこちら

１．閉じた他のファイルを参照している

SUMIFS関数が#VALUE!エラーになる原因の一つ目は「引数で閉じた他のエクセルファイルを参照している」ことです。

１－１．まずは例を確認

まずは、例を見ながら原因を確認してみましょう。

上の表は、Book1という名前のファイルにある「廃棄一覧表」です。

商品名や廃棄の有無、仕入れ額が入力されています。

この表をもとに別のファイル「Book2」で、「商品A廃棄の仕入額合計」をSUMIFS関数で合計すると…。

これは、SUMIFS関数の引数でBook2を参照しているからです。

１－２．原因を解説

なぜ、引数で他のファイルを参照するとエラーになるのでしょうか？

なぜなら、SUMIFS関数は「閉じているファイルの値をとることができない」からです。

もちろん、エクセル関数のすべてがそうではありません。閉じていてもしっかりと値を返してくれる関数はたくさんあります。

しかし、SUMIFS関数はそうではないのです。

つまり、実質SUMIFS関数では他のファイルを参照してはいけないのです。

１－３．数式作成時になぜ気づかないのか？

しかし、SUMIFS関数を使って数式を作っている時にはエラーになりませんよね。

実は、数式を組んだ直後には他ブックの参照元を閉じても即座にエラーにはならないのです。

なぜなら、他のファイルの値がメモリに残っているからです。

そして、閉じた後に再計算をかけたり一度数式が入ったブックを閉じて再度開くと、値がクリアされるため、エラーになるのです。

地球上でもっとも豊富な品揃え！
Amazonでのショッピングはこちら

１－４．【対処法１】SUM関数とIF関数を組み合わせて使う

対処法の一つ目は「他のファイル参照が可能な関数を使う」ことです。

「SUM関数とIF関数との組み合わせ＋配列数式」で、SUMIFS関数と同じことができます。

数式の形の比較をしてみます。

条件が2つの場合、SUMIFS関数は、次の通りです。

=SUMIFS(合計対象範囲,検索範囲1,条件1,検索範囲2,条件2)

対して、SUM関数＋IF関数＋配列数式の場合は次のようになります。

{=SUM(IF((検索範囲1=条件1)*(検索範囲2=条件2),合計対象範囲,""))}

ポイントは、以下の２つです。

ポイント

① 条件をカッコで囲み、「*」でつなげる
② 配列数式にする

特に②は、数式入力後に「CtrlキーとShiftキーを押しながらEnterキーを押す」ことで、配列数式になります。

例での数式で見比べてみると、SUMIFS関数の場合は次のようになります。

=SUMIFS([Book1.xlsx]Sheet1!$C$2:$C$7,[Book1.xlsx]Sheet1!$A$2:$A$7,"A",[Book1.xlsx]Sheet1!$B$2:$B$7,"〇")

対して、SUM関数＋IF関数＋配列数式の場合は以下の通りです。

{=SUM(IF(([Book1.xlsx]Sheet1!$A$2:$A$7="A")*([Book1.xlsx]Sheet1!$B$2:$B$7="〇"),[Book1.xlsx]Sheet1!$C$2:$C$7,""))}

１－５．【対処方法２】作業用シートに参照元をコピーする

対処法の二つ目は「Book2の作業用シートにBook1の表をコピー」することです。

どういうことかというと、次の通りです。

Book2に新しいシートを作成し、表などの体裁を整えた後、値は「＝」を使ってBook1の値を参照させるのです。

つまり、「Book2のシート2とBook1のシート1をリンクさせる」のです。

例えば、Book2のセルA2に入っている数式は、Book1のセルA2を見ています。

後は、このシートに対してSUMIFS関数で数式を作ればいいのです。

これだと、Book1が閉じていてもSUMIFS関数が#VALUE!エラーになることはありません。

オンライン学習プラットフォーム！
Excelの基本～達人レベルまで学べる！
◎【Udemy】◎

全て30日間返金保証付き
どんな講座があるか見たい方はこちら

⇒詳しく知りたい方はこちら

２．検索条件毎の行範囲が違う

SUMIFS関数が#VALUE!エラーになる原因の二つ目は「引数の行範囲が違っている」ことです。

２－１．まずは例を確認

まずは、例を見ながら原因を確認してみましょう。

上の表は、先ほどと同じものです。

ただ、今度は同じシートの中に結果を表示させたい表があります。

そして、「商品A廃棄の仕入額合計」をSUMIFS関数で合計しようとしたのですが…。

#VALUE!エラーになってしまいました。

これは、SUMIFS関数の「引数の行範囲が違う」ことが原因です。

２－２．原因を解説

「引数の行範囲が違う」とは、どういうことでしょうか？

それは、数式をクリックしてみると一目瞭然です。

例だと、赤枠が「検索範囲1」、紫枠が「検索範囲2」です。

紫枠のほうが、一行少ないのが分かります。

ダイアログを見ても、やはり行範囲が違うことが分かります。

また、合計対象範囲も同じです。

このように、引数同士の行範囲が違うと、SUMIFS関数はエラーになってしまうのです。

登録施設数No.1の総合旅行サイト
登録料・利用料・年会費が無料！
◎【楽天トラベル】◎

ホテル・旅館、航空券等の予約が可能！
楽天ポイントがどんどん貯まる！！
予約はこちら

２－２．【対処法】行範囲を合わせる

この原因の場合、対処法は簡単です。

「引数の行範囲を合わせればいい」のです。

例でいうと、合計条件範囲と条件範囲のすべてを「2行目から7行目」にします。

これで、#VALUE!エラーは解消されます。

以上、参考になれば幸いです。

【口コミ・評判のいいExcel本の紹介】

リンク

SUMIFS関数に関連したページは、次の通りです。。

SUMIFS関数　各ページへのリンクはこちら

関数の問題解決記事一覧へはこちら