SUMIFS・COUNTIFSの複雑な条件式はClaudeに設計させる【日付範囲・部分一致・OR条件対応】

※Excel+Claudeシリーズの1本です。

SUMIFSやCOUNTIFSでつまずくのは引数の順番ではなく、ほぼ全員が条件式（criteria）の書き方です。日付の範囲指定、部分一致、除外、OR条件——このあたりが絡んだ途端、検索で出てくるのは単純な例ばかりで、毎回同じことを調べ直すことになります。厄介なのは、条件式のミスがエラーにならず「合計が微妙に合わない」形で表に出ること。原因が分からないまま提出する事故はここから起きます。

この記事で手に入るのは次の3点です。

そのままコピペできるプロンプトテンプレート
SUMIFSでOR条件を書く方法（バージョン別の注意付き）
返ってきた数式を本番データで突合する検証手順

SUMIFS・COUNTIFSの複雑な条件式はClaudeに設計させる【日付範囲・部分一致・OR条件対応】のサンプル ▲ Excel+Claude サンプル

なぜSUMIFSの条件式は難しいのか

SUMIFSの条件は「セルの値」ではなく文字列で書いた小さな命令です。ここが直感に反します。

">="&$H$1 のように、比較演算子と参照を文字列連結する必要がある
">=2026/4/1" のような直書きは地域設定と日付書式に依存する。必ず ">="&$H$1（セル参照）か ">="&DATE(2026,4,1) に統一する
"<>返品" は「返品以外」だが、"<>" だけだと「空白以外」になる
OR条件はSUMIFS単体では書けない

つまり、SUMIFSが苦手なのではなく「Excel独自の条件文法」が覚えにくいだけ。AIに丸投げして良い領域です。

Claudeに渡すべき3つの情報

雑に「SUMIFSの書き方教えて」と聞くと一般論しか返りません。次の3点を必ず含めます。

表の構造（列名と列記号、ヘッダー行、データ範囲）
条件の日本語での言語化（曖昧さを残さない）
条件値をどこに置くか（セル参照か、数式に直書きか）

3つ目が特に重要です。これを伝えないと、条件をベタ書きした「使い回せない式」が返ってきます。

コピペOKのプロンプトテンプレート

Excelの数式を作ってください。

【表の構造】
・シート名: 売上
・A列:日付 B列:支店 C列:商品名 D列:区分 E列:金額
・1行目がヘッダー、データは2行目から5000行程度

【やりたいこと】
指定期間内・指定支店・商品名に特定の語を含む・区分が指定語以外
の合計金額を出したい

【条件値の置き場所】
H1:開始日 H2:終了日 H3:支店名 H4:商品キーワード H5:除外区分
（数式に直書きせず、必ずセル参照にしてください）

【出力してほしいもの】
1. 完成した数式（そのまま貼れる形）
2. 各条件が何を意味するかの1行解説
3. この式が壊れる可能性のあるケース

「3. 壊れるケース」を要求するのがコツです。前提の穴を先に教えてくれるので、後から数値が合わずに悩む時間が減ります。

実例1：SUMIFSで日付範囲・部分一致・除外条件をまとめて指定する

上のテンプレートに対しては、次のような3点セットが返ってきます。

1. 数式
=SUMIFS(売上!$E$2:$E$5000,
  売上!$A$2:$A$5000,">="&$H$1,
  売上!$A$2:$A$5000,"<="&$H$2,
  売上!$B$2:$B$5000,$H$3,
  売上!$C$2:$C$5000,"*"&$H$4&"*",
  売上!$D$2:$D$5000,"<>"&$H$5)
2. 各条件の意味 - ">="&$H$1 / "<="&$H$2：開始日以降かつ終了日以前 - $H$3：支店名の完全一致（比較演算子がない場合はクォート不要） - "*"&$H$4&"*"：商品名にキーワードを含む（ワイルドカードは式側に置く） - "<>"&$H$5：指定区分を除外

3. 壊れる可能性のあるケース - H4・H5が空欄のとき条件が意図せず変質する - A列に文字列型の日付が混在するとその行が集計されない

E:E のような列全体参照でも動きますが、5000行規模でも再計算が重くなります。実務では E2:E5000 かテーブル参照を使ってください。

実例1が壊れる3つのケース

Claudeが挙げた「壊れるケース」は、実際に踏むと原因が分かりにくいものばかりです。

$H$4（キーワード）が空："*"&""&"*" となり、文字列が入ったセルを全件ヒットさせます。逆に数値や空白の行はヒットしないため、合計が「なぜか少し多い」状態になります。
$H$5（除外語）が空："<>"&"" は "<>" に化け、条件が「空白以外の全件」に変わります。除外しているつもりで何も除外できていません。
A列に文字列型の日付が混在：">="&$H$1 はその行を無言でスルーします。エラーが出ないのが最も危険です。

対処としては、H4・H5が空なら条件を外した式に切り替える（IF分岐や別セルでの既定値設定）、日付列は事前に DATEVALUE で正規化する、が現実的です。

実例2：SUMIFSでOR条件（東京または大阪）を書く

実例1と同じ表・同じH1〜H5のまま、支店だけを「東京または大阪」に変えます。SUMIFSの条件を並べてもAND結合になるため、配列で渡して外側で合計する発想に切り替えます。

=SUM(SUMIFS(売上!$E$2:$E$5000,
  売上!$B$2:$B$5000,{"東京","大阪"},
  売上!$A$2:$A$5000,">="&$H$1,
  売上!$A$2:$A$5000,"<="&$H$2))

支店リストをセル範囲（例：J1:J3）に持たせる場合はこうです。

=SUMPRODUCT(SUMIFS(売上!$E$2:$E$5000,
  売上!$B$2:$B$5000,$J$1:$J$3,
  売上!$A$2:$A$5000,">="&$H$1,
  売上!$A$2:$A$5000,"<="&$H$2))

バージョン差に注意してください。

{"東京","大阪"} の配列定数は、旧バージョンでも SUM(SUMIFS(...)) の通常入力で動きます
$J$1:$J$3 のセル範囲は、Excel 365・2021以外では Ctrl+Shift+Enter（CSE）入力が必要です。CSEを避けたいなら SUMPRODUCT でくるみます

SUMPRODUCTは「365で使える上位版」ではなく、旧環境でCSE入力を回避するための手段です。ここを逆に覚えていると、配布先で動かない式を作ってしまいます。

COUNTIFSの空白・空文字の数え分け

COUNTIFSは条件文法こそ同じですが、「数えたつもりが数えられていない」が起きやすい関数です。特に空白まわりは、条件式によって結果が変わります。

目的	条件式	数式が返した空文字("")は
真の空白のみ数える	`"="`	数えない
空白＋空文字を数える	`""`	数える
空白でないセルを数える	`"<>"`	数える（非空白として扱われる）

"" と "=" は同じではありません。IFやVLOOKUPの結果として "" が入った列では、見た目が空でも "<>" は「空白でない」と判定します。空セルと空文字を区別したいかどうかを、プロンプトに明記してください。

その他のCOUNTIFS条件式も押さえておきます。

目的	条件式
アスタリスクそのものを含む	`"~*"`
0より大きい	`">0"`
特定セルの値と一致	`$H$3`（クォート不要）

Claudeの回答を検証する4ステップ

AIが出した数式をそのまま本番シートに貼るのは危険です。次の順で確認します。

10行程度の小さなサンプルを作り、手計算した答えと突き合わせる
条件を1つずつ削り、日付・部分一致・除外がそれぞれ効いているかを個別に確認する
想定外データ（空白行、全角スペース、文字列型の日付）を1行混ぜて挙動を見る
本番データで突合する——条件を全部外した単純な SUM から始め、条件を1つ足すごとに合計がいくら減ったかを確認する。その減り方を自分の言葉で説明できれば、式は正しく効いています

4番が実務では決定的です。「サンプルでは合っていた」で止めると、本番特有の表記ゆれや型混在を見逃します。3番や4番でズレたら、原因を添えて「A列に文字列型の日付が混在するケースにも対応させて」と再依頼すれば、DATEVALUE を挟む形やSUMPRODUCTへの書き換えが返ってきます。