SUMIFSの合計が合わない。条件の書き方をAIと見直す観点

先に押さえる、SUMIFSのルール

SUMIFS関数のMicrosoft公式ヘルプでは、書式は =SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], …) とされています。条件範囲は合計対象範囲と同じ行数・列数にします。条件には "<>バナナ" のように演算子を含められ、テキストの条件は引用符で囲みます。* と ? はワイルドカードで、記号そのものを探すときは前に ~ を付けます。

COUNTIFS関数の公式ヘルプには、"<" & A6 のようにセル参照を演算子とつなぐ書き方が載っています。COUNTIFSは同じ形の条件で「件数」を数えます。SUMIFSの条件をそのまま移して件数を見ると、条件側の問題か合計対象側の問題かを切り分けられます。

疑う順番

① 範囲のずれ

最初に見るのは範囲です。サイズが違えば公式ヘルプの要件を満たしませんが、行数が同じで開始行だけずれている場合は、そのまま計算されてしまいます。

=SUMIFS(D2:D100, B3:B101, "田中")

合計対象は2行目から、条件は3行目から始まっています。1行ずれて、別の行の金額を足す形です。ほかにも次の原因があります。

• 数式を下にコピーしたら参照が動いた($D$2:$D$100 のように固定していない)
• データが150行目まであるのに、範囲が100行目で止まっている
• 列を挿入して、条件範囲だけ別の列を指している

対策として、表をテーブルにするか、データより余裕のある範囲で全条件範囲の開始行と終了行をそろえる方法があります。これは編集上の提案で、公式ヘルプの手順ではありません。

② 文字と数値の違い

合計対象が文字として入っている場合。 文字列として保存された数値の公式ヘルプでは、見分けるポイントとして、左揃えになることと、セルに緑色のエラーインジケーターが出ることが挙がっています。変換方法は、エラーインジケーターから「数値に変換」、VALUE関数、「形式を選択して貼り付け」の3つです。

見た目で判断しにくいときは、金額列に次の式を使い、数値でないセルの数を調べます。

=COUNTA(D2:D100)-COUNT(D2:D100)

0以外なら、文字のセルが混じっています。空文字を返す数式のセルも数えられるので、見つかったセルは中身を確認してください。

条件側の文字ゆれ。 「田中」と「田中 」(末尾に空白)は、見た目が近くても別の文字列です。次の式で長さを比べると、余分な空白を見つけられることがあります。

=LEN(B2)-LEN(TRIM(B2))

1以上なら余分な空白があります。ただし、全角と半角の違いや見えない文字、全角スペースは見つけられない場合があります。そのときは、COUNTIFSで「田中」の件数を出し、フィルターで目視した件数と比べます。

③ 日付条件

日付は「数値(シリアル値)として入っているか」と「条件の書き方」の2点を確認します。

日付や数値の条件カウントの公式ヘルプには、">=1/1/2010" のように日付を文字で書く例が載っています。ただ、文字表記が環境でどう解釈されるかを避けたいなら、DATE関数やセル参照を使うほうが安全です。これは編集上の提案です。

=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,10,1), A2:A100, "<"&DATE(2026,11,1))

月末を "<="&DATE(2026,10,31) で終えると、時刻付きのデータ(10/31 15:00 など)が漏れます。10/31 15:00は、10/31 0:00より後だからです。「翌月1日より前」と書けば、時刻の有無にかかわらず月末までを含められます。

日付列そのものが文字の場合(「2026/10/05」が左揃えなど)は、条件を直しても合いません。=COUNT(A2:A100) がデータの行数より少なければ、日付になっていない行(または空白行)があります。

条件の書き方パターン集(説明用)

範囲は、$B$2:$B$100(担当)、$D$2:$D$100(金額)、$A$2:$A$100(日付)とします。表は「条件」の部分だけを示しています。

• 完全一致(文字)、条件の書き方："田中"、注意点：空白や表記が違うと一致しないことがある
• セルの値と一致、条件の書き方：$G2、注意点：条件セルの空白や表記ゆれも影響する
• 田中以外、条件の書き方："<>田中"、注意点：演算子ごと引用符で囲む
• 「田」で始まる、条件の書き方："田*"、注意点：* は任意の文字列
• 「交通」を含む、条件の書き方："*交通*"、注意点：拾いすぎに注意
• * や ? 自体、条件の書き方："~*"、注意点：~ を前に付ける
• 1,000以上、条件の書き方：">=1000"、注意点：数値の演算子は引用符の中
• セルの値以上、条件の書き方：">="&$G2、注意点：& でつなぐ
• 指定日以降、条件の書き方：">="&DATE(2026,10,1)、注意点：文字の日付の直書きは避ける
• 期間内、条件の書き方：日付条件を2組、注意点：終端は「翌日より前」

合計が合わないときのチェック表

上から順に確認し、見つかったら直して再計算します。

• 1、確認すること：合計対象と条件範囲の開始行・終了行は同じか、確認の方法：数式バーで参照をクリックして枠を見る、見つかったら：範囲をそろえ、$ で固定
• 2、確認すること：範囲がデータの最終行まで届いているか、確認の方法：最終行の行番号と数式を比べる、見つかったら：範囲を延長
• 3、確認すること：合計対象に文字のセルがないか、確認の方法：=COUNTA(範囲)-COUNT(範囲)、見つかったら：数値に変換
• 4、確認すること：条件列に空白や表記ゆれがないか、確認の方法：LEN と TRIM の比較、COUNTIFSの件数、見つかったら：文字を統一
• 5、確認すること：日付列は日付(数値)か、確認の方法：=COUNT(日付範囲)、見つかったら：日付に変換
• 6、確認すること：期間の終端と時刻は大丈夫か、確認の方法：終端を「翌日より前」にして再計算、見つかったら：条件を修正
• 7、確認すること：比べている相手と前提は同じか、確認の方法：フィルター表示の合計か、全行の合計か、見つかったら：前提をそろえる
• 8、確認すること：同じ条件でCOUNTIFSの件数は想定どおりか、確認の方法：手で数えた件数と比較、見つかったら：件数が違う条件を調べる

件数は合っているのに合計だけ違うなら、合計対象側(1〜3)を先に見ます。件数が違うなら、条件側(4〜6)を優先します。

AIに頼むときの依頼文と、人が確認すること

渡すのは、数式、列の見出しと型の説明、説明用に置き換えたサンプルの数行、期待値と実際の値です。顧客名や金額などの実データはそのまま貼らず、架空の値に置き換えてください。

説明用の依頼文です。

次のSUMIFS式の合計が期待値と合いません。原因の候補を、
①範囲のずれ ②文字と数値の違い ③日付条件 の順に挙げ、
それぞれ私が自分のExcelで確認できる手順(COUNTやCOUNTIFSなど)を
付けてください。断定せず、確認前は「可能性」と書いてください。

数式:=SUMIFS(D2:D100,B2:B100,"田中",A2:A100,">="&DATE(2026,10,1),A2:A100,"<="&DATE(2026,10,31))
列:A=日付、B=担当、D=金額(10/31 の行には時刻が付いている場合がある)
期待値:70,000 / 実際:30,000
サンプル(架空の3行):
2026/10/05, 田中, 30000
2026/10/31 15:00, 田中, 22000
2026/10/12, 田中 , 18000

この例では3行すべてを足すと70,000になるはずが、数式の結果は30,000です。10/31 15:00の行は終端条件から漏れ、末尾に空白のある「田中 」の行は条件の「田中」と一致しません。AIの回答にこの2点が候補として挙がっていれば、チェック表の4と6で自分のブックを確かめます。挙がらなくても、チェック表を順に確認すれば拾えます。

AIの回答はあくまで仮説です。修正式を採用する前に、次の2つを必ず手元で照合してください。

• 件数:COUNTIFSの結果と、フィルターや目視で数えた件数が一致するか
• 合計:修正後の結果と、フィルターで絞った行を手で足した合計が一致するか

どちらも一致して初めて、その条件での修正は確認できたことになります。一致しなければ、チェック表の1から順にやり直してください。