ExcelのSUMIFS関数は、複数の条件をすべて満たすデータを特定の範囲から探し出し、対応する数値データの合計を計算するための非常に強力で便利な関数です。単一条件の合計に使うSUMIF関数よりも複雑な集計が可能で、データ分析やレポート作成の効率を大幅に向上させます。本記事では、SUMIFS関数の基本的な構文から、複数条件(AND条件)、OR条件の指定方法、さらには実用的なヒントまで、詳しく丁寧に解説します。
SUMIFS関数の基本的な使い方
SUMIFS関数は、指定した複数の「条件範囲」の中からそれぞれの「条件」に一致する行を特定し、それらの行に対応する「合計対象範囲」の数値の合計を返します。
構文
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
- 合計対象範囲 (sum_range): 必須。実際に合計したい数値が含まれるセル範囲を指定します。(例: `C2:C10`)
- 条件範囲1 (criteria_range1): 必須。1つ目の条件で検索するセル範囲を指定します。(例: `B2:B10`)
- 条件1 (criteria1): 必須。1つ目の条件範囲内で検索する条件を指定します。数値、文字列、日付、セル参照、または比較演算子を含む文字列(例: `”関東”`, `”>=400″`, `A1`)で指定します。
- [条件範囲2, 条件2], …: 任意。2つ目以降の条件範囲と条件のペアを指定します。最大127個の条件ペアまで指定可能です。
重要: 合計対象範囲
とすべての 条件範囲
は、同じ行数・列数である必要があります。サイズが異なると `#VALUE!` エラーが発生します。
基本的な使用例
以下のような売上データ(A1:C5セルにあると仮定)があるとします。
名前 (A列) | エリア (B列) | 売上 (C列) |
---|---|---|
田中 | 関東 | 500 |
鈴木 | 関西 | 300 |
佐藤 | 関東 | 450 |
高橋 | 東北 | 600 |
このデータから「関東」エリアの売上合計を求めるには、次のように入力します。
=SUMIFS(C2:C5, B2:B5, "関東")
C2:C5
: 合計したい売上データの範囲。B2:B5
: 条件(”関東”)で検索するエリアデータの範囲。"関東"
: 検索する条件。
結果: 500 (田中) + 450 (佐藤) = 950
複数条件の指定方法 (AND条件)
SUMIFS関数の最大の特長は、複数の条件をすべて満たす (AND条件) データを集計できることです。条件ペアを追加していくだけで、AND条件として扱われます。
例: エリアが「関東」かつ売上が400以上
上記のデータ表で、関東エリアで売上が400以上の合計を求めるには、次のように条件ペアを追加します。
=SUMIFS(C2:C5, B2:B5, "関東", C2:C5, ">=400")
C2:C5
: 合計対象範囲 (売上)。B2:B5, "関東"
: 1つ目の条件ペア (エリアが関東)。C2:C5, ">=400"
: 2つ目の条件ペア (売上が400以上)。
結果: 500 (田中) + 450 (佐藤) = 950 (両方の条件を満たす)
OR条件の指定方法
SUMIFS関数は、標準ではAND条件で動作します。OR条件(いずれかの条件を満たす)を指定するには、いくつかの工夫が必要です。
方法1: 同じ列に対するOR条件 (配列定数を使う)
同じ列に対して複数のOR条件を指定する場合(例: エリアが「関東」または「関西」)、配列定数 {}
を使うのが最もスマートです。
例: エリアが「関東」または「関西」の売上合計
=SUM(SUMIFS(C2:C5, B2:B5, {"関東","関西"}))
{"関東","関西"}
: 条件として配列定数を指定します。これにより、SUMIFSは「関東」の合計と「関西」の合計をそれぞれ計算した配列を返します。SUM(...)
: SUMIFSが返した配列(各条件ごとの合計)を、最後にSUM関数で合計します。
結果: [950 (関東), 300 (関西)] という配列が内部的に作られ、SUM関数で合計されて 1250 となります。
方法2: 異なる条件の合計を加算 (SUMIFSを複数回使う)
異なる種類の条件に対するOR条件のような集計(例: 「関東エリアの合計」または「売上500以上の合計」)を行いたい場合、それぞれの条件でSUMIFS関数を計算し、その結果を単純に足し合わせる方法があります。
例: 「関東エリアの合計」と「売上500以上の合計」を足し合わせる
=SUM(SUMIFS(C2:C5, B2:B5, "関東"), SUMIFS(C2:C5, C2:C5, ">=500"))
解説: SUMIFS(C2:C5, B2:B5, "関東")
は関東の合計 (950) を計算し、SUMIFS(C2:C5, C2:C5, ">=500")
は売上500以上の合計 (500 + 600 = 1100) を計算します。これらをSUM関数(または単純な `+`)で合計します。
結果: 950 + 1100 = 2050
注意: この方法は、両方の条件を満たすデータ(例: 関東エリアで売上500以上の田中さんのデータ)が重複してカウントされることに注意してください。純粋なOR条件(どちらかの条件を満たす行を重複なく合計)とは異なります。
方法3: 複雑な条件の組み合わせ (方法1とAND条件の組み合わせ)
複数のOR条件(同じ列)とAND条件(他の列)を組み合わせることも可能です。
例: エリアが「関東」または「関西」で、かつ売上が400以上の合計
=SUM(SUMIFS(C2:C5, B2:B5, {"関東","関西"}, C2:C5, ">=400"))
解説: 配列定数を使ってエリア条件を指定し、さらに売上条件を追加します。SUMIFSは「関東で400以上」の合計と「関西で400以上」の合計の配列を返し、それをSUM関数で合計します。
結果: [950 (関東&400以上), 0 (関西&400以上)] という配列が作られ、合計は 950 となります。
(元の記事の例 `=SUM(SUMIFS(C2:C5, B2:B5, “関東”, C2:C5, “>=400”), SUMIFS(C2:C5, B2:B5, “関西”, C2:C5, “>=400”))` も同じ結果になりますが、配列定数を使う方が簡潔です。)
条件での比較演算子とワイルドカード
条件 (criteria) の指定には、比較演算子やワイルドカードも利用でき、より柔軟な条件設定が可能です。
- 比較演算子:
">500"
: 500より大きい"<=1000"
: 1000以下"<>0"
: 0以外 (等しくない)"<>東京"
: 文字列「東京」以外">"&A1
: セルA1の値より大きい(セル参照と組み合わせる場合)
- ワイルドカード (文字列検索用):
"田中*"
: 「田中」で始まる文字列(例: 田中太郎, 田中商事)"*工業"
: 「工業」で終わる文字列(例: 佐藤工業, ABC工業)"*システム*"
: 「システム」を含む文字列"??木"
: 任意の2文字+「木」で構成される3文字の文字列(例: 鈴木, 高木)
ワイルドカード文字 (`*`, `?`, `~`) 自体を検索したい場合は、チルダ (`~`) を前につけます (例: `~*`, `~?`, `~~`)。
SUMIFS関数の注意点とベストプラクティス
- 範囲のサイズ:
合計対象範囲
とすべての条件範囲
の行数・列数が完全に一致している必要があります。ずれていると `#VALUE!` エラーになります。 - 空白セルの扱い: 条件で空白セルを指定する場合は
""
(空の文字列) ではなく"="
を使います。空白でないセルは"<>"
を使います。 - 大文字/小文字: 文字列の条件では、大文字と小文字は区別されません(”関東” と “かんとう” は区別されますが、”Tokyo” と “tokyo” は区別されません)。
- 数値と文字列: 見た目が数字でも文字列として格納されているデータは、数値の条件では一致しません。データ型を確認しましょう。
- パフォーマンス: 非常に大きなデータ範囲(数十万行以上)に対して多数のSUMIFS関数を使用すると、再計算に時間がかかることがあります。必要に応じて計算方法を手動にしたり、Power Pivotなどの利用を検討したりしましょう。
SUMIF関数との違い
SUMIF関数は、単一の条件に基づいて合計を計算する関数です。SUMIFS関数はSUMIF関数の上位互換のような位置づけで、複数の条件を扱えます。
- SUMIF構文: `=SUMIF(条件範囲, 条件, [合計範囲])`
- 合計範囲は省略可能(省略時は条件範囲が合計される)。
- 引数の順番がSUMIFSと異なります(合計範囲が最後)。
- 使い分け:
- 条件が1つだけの場合は、SUMIF関数の方がシンプルです。
- 条件が2つ以上ある場合は、SUMIFS関数を使用します。
- SUMIFS関数は条件が1つの場合でも使用できるため、「常にSUMIFSを使う」とルール化するのも一つの方法です。
まとめ
SUMIFS関数は、複数のAND条件に基づいて特定のデータを効率的に集計するためのExcel/スプレッドシートにおける必須関数のひとつです。基本的な使い方から、配列定数を使ったOR条件の指定、比較演算子やワイルドカードの活用までマスターすれば、複雑なデータ分析やレポート作成が格段にスムーズになります。引数の指定順序や範囲のサイズに注意し、ぜひこの強力な関数を日々の業務で活用してみてください。
コメント