COUNTIFの結果が"#VALUE"になるエラーの解決法

こんにちは、きくはなです。
最近業務でExcelに触れる機会があったのですが、せっかくなので知見を共有します。

結論

SUM関数とIF関数を組み合わせた数式を代用する。

//変更前
=COUNTIF([Source]Sheet1!$A$1:$A$8,"a")

//変更後
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))

そもそものおはなし

COUNTIF関数は、配列から条件に合う要素の数をカウントできる関数です。
しかし、別シートや外部ブックを参照した場合、"#VALUE"が返されてしまうことがあるようです。
（原因はよく分かりませんでした...）

公式ドキュメントを参照すると、別の関数を組み合わせた代用式を用いることを推奨しています。

詳細な解説

公式ドキュメントでは変更前と変更後の数式が提示されているだけなので、少し細かく分析してみましょう。

先ほどの例をもとに解説すると、

//変更前
=COUNTIF([Source]Sheet1!$A$1:$A$8,"a")

//変更後
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))

COUNTIF関数は、配列から条件に合う要素の数をカウントできる関数です。
変更後の数式は、まずIF関数で条件に合う要素を1,それ以外を0に変換してから、SUM関数で合計しています。
1の集まりを、条件に合う要素分足すので、そのまま条件に合う要素の数になる、という仕組みですね。

余談ですが、SUMIF、COUNTBLANK関数でも似たような問題が起こるそうです。こちらについてもSUMとIFの組み合わせで解決可能なようです。

SUMIF

//変更前
=SUMIF([Source]Sheet1!$A$1:$A$8,"a",[Source]Sheet1!$B$1:$B$8)

//変更後
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",[Source]Sheet1!$B$1:$B$8,0))

COUNTBLANK

//変更前
=COUNTBLANK([Source]Sheet1!$A$1:$A$8)

//変更後
=SUM(IF([Source]Sheet1!$A$1:$A$8="",1,0))