エクセル関数応用
「SUMIFSで動くのにXLOOKUPでエラー？」型不一致の関数別挙動

例えば、VLOOKUP/XLOOKUPでは、検索値と検索範囲のデータ型（数値と文字数字）が一致していないと「#N/A」となる。
画像A列は、奇数は数値、偶数は文字数字。
この違いでXLOOKUPは検索値なしのエラーとなる。

=XLOOKUP(D2,A:A,B:B)
この数式の解決方法としては、「数値」か「文字数字」のどちらかに統一すればよい。
=XLOOKUP(D2&"",A:A&"",B:B)
=XLOOKUP(--D2,--A:A,B:B)
これなら、検索値と検索範囲のデータの型（数値／文字数字）がどちらでも期待通りに一致してくれます。

では、COUNTIF/COUNTIFS/SUMIF/SUMIFSではどうなのか？代表としてSUMIFSで例示します。
=SUMIFS(B:B,A:A,D2)
この数式で問題なく条件通りに集計されます。
XLOOKUPとSUMIFSのこの挙動の違いは、分かりづらいし勘違いしやすい部分ですが、しっかりと認識してください。

SUMIFSがデータ型（数値と文字数字）に関係なく条件集計してくれることは分かりました。
ですが、念のためにデータ型をどちらかに統一しておいた方が無難なのではないか。
そう考えたくなりますよね。
=SUMIFS(B:B,--A:A,--D2)
「この数式には問題があります。」
なんと、入力することも出来ない…

「なぜエラー？なぜ入力すらできないのか？」
SUMIFS（COUNTIF/COUNTIFS/SUMIF）の条件範囲や合計範囲には、生の「セル参照」しか指定できません。
--A:A や A:A&"" のような加工を加えると「セル参照」ではなく「配列の計算式」と判定され、構文エラー（入力不可）になります。

Excelにおける「数値」と「文字数字」の不一致に起因するエラーや挙動の違いについて。
✅検索関数（XLOOKUP/VLOOKUP）の挙動

✅集計関数（SUMIFS/COUNTIFS等）の挙動

Excelは関数の役割（検索か集計か）によって、内部の型評価や引数の受け取り方が全く異なる設計になっています。
単に「動く数式を組む」だけでなく、各関数の設計思想の違いまで深く把握しておくことが、潜在的な計算エラーを未然に防ぎ、誰が扱っても崩れない効率的な数式設計を可能にします。

同じテーマの記事
新着記事
アクセスランク