SUMIF関数は、一つの条件に合う行の数値を合計します。基本形は=SUMIF(条件範囲,条件,合計範囲)です。たとえば=SUMIF(A2:A100,"東",C2:C100)は、A列が「東」の行にあるC列の金額を合計します。条件範囲と合計範囲の先頭・行数をそろえ、比較演算子、ワイルドカード、日付を正しく指定することが重要です。二つ以上の条件をすべて満たす合計にはSUMIFSを使いますが、SUMIFとSUMIFSは引数の順番が異なります。結果が合わないときは数値に見える文字列、空白、範囲ずれ、外部ブック、計算設定を確認します。
SUMIFの三つの引数
=SUMIF(B2:B20,"商品A",D2:D20)では、B2:B20が条件を調べるrange、”商品A”がcriteria、D2:D20が加算するsum_rangeです。条件に一致したB列の各行と対応するD列を合計します。sum_rangeを省略すると、条件を調べたrange自体の数値が合計されます。
COUNTIFは件数、SUMIFは金額などの合計です。単純な全件合計はSUM、複数条件はSUMIFSを使います。一列の範囲に見出しや小計行を含めると二重計上の原因になります。データ行だけを対象にし、途中のSUBTOTALや手入力合計が混在していないか確認します。
固定条件と条件セルを使い分ける
固定文字は=SUMIF(A2:A100,"完了",C2:C100)、条件をF2へ置くなら=SUMIF(A2:A100,F2,C2:C100)とします。条件セル方式なら、入力規則で許可値を選ばせ、数式を編集せず集計できます。数式を横や下へコピーするときは、データ範囲を絶対参照にし、条件セルだけを意図どおり動かします。
文字列の一致では、先頭・末尾空白、全角・半角、非表示文字、数値文字列が結果へ影響します。TRIMやCLEANで補助列を作り、元データの意味を確認してから正規化します。SUMIFは条件列の色やフォントを条件に合計する関数ではないため、状態は色ではなくセル値へ保存します。
比較演算子とセル参照を連結する
50,000円以上を合計する例は=SUMIF(B2:B100,">=50000",B2:B100)です。基準をF2へ置くなら=SUMIF(B2:B100,">="&F2,C2:C100)のように、演算子を引用符で囲み、&でセル参照と結合します。以上と超、以下と未満の違いを業務ルールへ合わせます。
表示値が丸められていても条件は内部値で評価されます。画面で50,000に見える49,999.6が対象外になることがあります。丸め後を基準にするなら、補助列でROUNDした値を作るなど判定規則を明示します。負数、返品、値引きを合計へ含めるかも決めます。
ワイルドカードで部分一致する
文字条件では*が任意の文字列、?が任意の一文字に一致します。=SUMIF(A2:A100,"*東京*",C2:C100)はA列に「東京」を含む行のC列を合計します。実際のアスタリスクや疑問符を検索する場合は、チルダを前へ置きます。部分一致は「東京都」「東京支店」「新東京」などを広く拾う点に注意します。
商品コードや部門コードは完全一致が安全です。部分一致を使うなら、対象候補をフィルターして目視確認します。複数の部分条件をSUMIFの足し算で表すと、同じ行が両条件に一致して二重計上される場合があります。OR条件の重複を除く必要があれば、補助列や別の集計方法を使います。
日付・月間・期間を集計する
指定日以降は=SUMIF(A2:A100,">="&F2,C2:C100)のように、F2へ正しい日付値を置きます。一か月の範囲は開始日以上かつ翌月初日未満という二条件なので、SUMIFSを使います。日付文字列を数式へ直接書くより、DATE関数または日付セルを使う方が地域設定の差を減らせます。
日時が入る列では、月末日以下より翌月初日未満の方が、月末日の時刻を含めやすくなります。日付に見える文字列、空白、エラーを確認し、正規の日付値へ変換します。TODAYを使う集計は再計算日で変わるため、締め処理では基準日セルを固定し、集計日時を表示します。
条件範囲と合計範囲をそろえる
SUMIFのrangeとsum_rangeは、対応する左上セルと行・列の関係をそろえます。A2:A100を条件範囲にしたら、合計範囲も対応するC2:C100とします。ずれたC3:C101では別行の金額が対応し、見た目上もっともらしい誤集計になります。表の先頭と末尾を同時に確認します。
Microsoftのエラー案内ではSUMIFSの各条件範囲と合計範囲は同じ行列数が必要とされています。SUMIFには異なるサイズ範囲に関する挙動がありますが、予測しにくい式へ依存せず、実務では必ず同じ形へそろえます。列追加や行追加に追従させるならExcelテーブルの構造化参照を使います。
複数条件はSUMIFSへ切り替える
「地域が東」かつ「商品がA」の売上なら、=SUMIFS(C2:C100,A2:A100,"東",B2:B100,"商品A")とします。SUMIFSは最初にsum_range、その後へcriteria_rangeとcriteriaの組を並べます。SUMIFでは合計範囲が第3引数なので、切り替え時の順番間違いに注意します。
SUMIFSの条件は基本的にANDです。「東または西」は二つのSUMIFSを足す方法がありますが、条件の重複を確認します。多数条件や可変集計では、ピボットテーブル、Power Query、データモデルなどの方が監査しやすい場合があります。長い式を一セルへ隠す前に、集計要件を表へ整理します。
数値に見える文字列とエラーを確認する
sum_rangeに文字列として保存された数字があると、期待どおり加算されないことがあります。ISNUMBER、警告マーク、数式バーで型を確認します。通貨記号や桁区切りを文字として取り込んだデータは、変換ルールを決めて数値へ直します。空白、ハイフン、”未定”を0と同一視するかも業務定義が必要です。
条件範囲や合計範囲にエラー値がある場合、結果もエラーになることがあります。IFERRORで全体を0へ置き換える前に、どの行のどのエラーかを確認します。売上が0なのか、データ取得失敗なのかを区別できるよう、エラー件数を別セルへ表示します。
外部ブックと長い条件の注意
Microsoftの公式トラブルシューティングでは、閉じた別ブックを参照するSUMIF・SUMIFSが#VALUE!になる場合があると案内されています。参照元を開くと動く式は、定期報告や自動更新で不安定です。必要データを同じブックの管理されたテーブルへ取り込むか、Power Queryなど更新状態を管理できる方法を検討します。
同資料では長い条件文字列の連結が#VALUE!につながる場合も説明されています。識別子が長い場合、不要な空白や非表示文字を含めて一致条件を確認します。エラーを0へ隠さず、参照元ブック名、最終更新日時、接続状態を集計結果とともに表示します。
検算と保守しやすい設計
SUMIFの結果は、条件列をフィルターして表示行の金額をSUBTOTALで確認する、ピボットテーブルで同じ分類を集計する、少量データを手計算するなど複数方法で検算します。0件、1件、境界値、負数、空白、文字列数値、重複、小計行を含むテストデータを用意します。
基準値、対象期間、通貨、税区分、更新日時を集計表へ明記します。条件や列位置が変わったときに数式が追従するか確認します。数式を値へ貼り付けて根拠を消さず、承認済みの基準表と入力データを保存します。大規模データで列全体参照を多数使う場合は計算時間も測定します。
確認チェックリスト
- SUMIFの条件範囲・条件・合計範囲を正しい順で指定する
- 条件範囲と合計範囲の先頭・末尾・行列数をそろえる
- 比較演算子、条件セル、日付、ワイルドカードを正しく指定する
- 複数条件ではSUMIFSの引数順とAND・ORの違いを確認する
- 数値文字列、空白、エラー、負数、小計行を確認する
- フィルター、SUBTOTAL、ピボットなどで合計を検算する
コメント