COUNTIFS関数が＃VALUE!エラーになる２つの原因と対処法

ここでは、ExcelのCOUNTIFS関数で「#VALUE!エラー」になってしまう原因と対処法を解説します。

中には原因が分かりづらいものもありますので、COUNTIFS関数を使いこなせるようになるためには必要な知識になってくると思います。

COUNTIFS関数は「引数で閉じた他のエクセルファイルを参照している」と、#VALUE!エラーになります。

１－１．例を見ながら原因を確認

では、例を見てみましょう。

上の表は部署名や氏名、性別の出席確認を入力したBook1の「出席確認表」です。

この表をもとに別のファイル「Book2」で、「事務部の女性の出席数」をCOUNTIFS関数でカウントすると…。

#VALUE!エラーになりました。

このように、COUNTIFS関数で他のエクセルファイルを参照・指定していると、#VALUE!エラーになるのです。

例では「カウント元の表」と「結果を表示させる表」が別のファイルになっていて、COUNTIFS関数の引数「検索条件範囲」が「カウント元になる表」を参照しています。

ではなぜ、COUNTIFS関数で他のファイルを参照するとダメなのでしょうか？それは、仕様としか言えないようです。

一応、参照元のファイルをその都度開けばエラーは解消されます。しかし、とても手間ですよね。

そして、エクセルの関数の中にはこのような関数が複数あり、次の通りです。

エクセル関数で#VALUE!エラーを起こす原因の中には、このようなものもあるということを知っておいてください。

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

でも、数式を作っている時にはエラーになりませんよね。

それは「数式を組んだ直後には参照元のファイルを開いている」、また閉じたとしても「値のデータが残っている」からです。

これが、原因を分かりづらくしている要因の一つです。

なお、数式作成後には「参照先のファイルを閉じ、数式の入ったセルをクリックしてEnterを押した時」や、「数式を入力したファイルを保存して閉じて、再度開き直した時」に初めて#VALUE!エラーとなります。

ちょっと意地悪ですね…。

１－３．【対処方法１】SUMPRODUCT関数を使う

では、他のファイルを開かなくてもエラーを出さないようにするにはどうしたらよいでしょうか？

対処法の一つ目は「SUMPRODUCT関数」を使うことです。

SUMPRODUCT関数の使い方は簡単で、次の通りです。

COUNTIFS関数の構文は、

=COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2)

※ 例：=COUNTIFS([Book1.xlsx]Sheet1!$A$3:$A$7,A3,[Book1.xlsx]Sheet1!$C$3:$C$7,B3)

です。

対してSUMPRODUCT関数は、

=SUMPRODUCT((検索条件範囲1=検索条件1)*(検索条件範囲2=検索条件2))

※ 例：=SUMPRODUCT(([Book1.xlsx]Sheet1!$A$3:$A$7=A3)*([Book1.xlsx]Sheet1!$C$3:$C$7=B3))

です。

つまり、SUMPRODUCT関数では「カッコ内に条件式を書き、条件式同士を『*（アスタリスク）』でつなげばいい」のです。

なお、条件が一つの場合、SUMPRODUCT関数は、

=SUMPRODUCT((検索条件範囲1=検索条件1)*1)

のように、条件式に「*1」を付けることに注意してください。

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

対処法の二つ目は「Book2の作業用シートに参照元のデータをコピー」することです。

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

上の表はカウント結果を表示させたいBook2にBook1のシートの内容をコピーしたものです。

B22k2に新しいシートを作成し、表などの体裁を整えた後、値は「＝」を使ってBook1の値とリンクさせます。

例えば、このシートのセルA3に入っている数式は、Book1のセルA3を見ています。

後は、このシートに対してCOUNTIFS関数を使ってカウントすればいいのです。

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

そして、Book1の値が変わってもBook2の値も変わります。

デメリットは「ファイル容量が大きくなること」と「作業用のシートが必要になること」ですが、参照元のファイルがよほど大きくない限り、そこまで大きな問題点にはならないでしょう。

１－５．SUMPRODUCT関数とはどう使い分ける？

SUMPRODUCT関数を使う場合、COUNTIFS関数とはどういう基準で使い分ければいいのでしょうか？

個人的には、COUNTIF関数のほうが直感的に分かりやすくて使いやすいです。なぜならSUMPRODUCT関数は他にも色々なことができるので、パッと見なにをしようとしているのかが分からないからです。

しかし、エラーになっては元も子もありませんから、「他のファイルを参照しなければCOUNTIFS関数、参照するならばSUMPRODUCT関数を使う」のが、いいと思います。

COUNTIFS関数は、「検索条件範囲ごとの行範囲が違っている」と#VALUE!エラーになります。

２－１．例を見ながら原因を確認

まずは、例を見てみましょう。

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

この表をもとに「事務部の女性の出席数」をCOUNTIFS関数でカウントします。なお、さっきと違って同じファイルの中でのカウントです。

しかし、結果は＃VALUE!エラーになってしまいました。

よく見ると、数式内の2つの「検索条件範囲」の行範囲が違っています。

例では、検索条件範囲1が「3行目から8行目（A3:A8）」、検索条件範囲2が「3行目から7行目（C3:C7）」となっています。

このように、COUNTIFS関数は「検索条件範囲」同士の行範囲が違うと＃VALUE!エラーになるのです。

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

この場合、対処法は簡単です。検索条件範囲の行範囲を合わせればいいのです。

、例でいうと参照元の表は3行目から7行目までの範囲だったので、検索条件範囲もすべて「3行目から7行目」に合わせます。

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

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

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

• COUNTIFS関数 使いこなすためのポイントはこちら

• ◎基本的な使い方
• ◎空白以外を複数条件の一つにする方法
• ◎以上・以下などの範囲・期間指定を条件にする
• ◎検索条件をまとめて切り換える方法
• ◎検索条件範囲を複数列指定する方法
• ◎＃VALUE!エラーになる２つの原因と対処法
• ◎複数条件をまたは（OR条件）にする方法

関数の解説記事一覧へはこちら