複雑な条件でも一発で合計

Excelで複数の条件を指定して数値を合計したいときは、「SUMIFS」（サム・イフス）関数を使うのが定番です。特定の文字列、数値や日付の比較、部分一致での抽出といった、複数の条件を指定して数値を合計できます。

例を挙げて説明しましょう。以下のようなセミナーの開催日や参加費、参加者数の表があるとして、『参加費が「2,000円」以上かつ「3,000円」未満』という条件で、参加者数の合計を求めたいとします。

セミナーの開催日や参加費、参加者数などがまとまっている表です。見出し行から分かるように、表にはフィルターが設定されています。

このとき、表に設定されたフィルター機能を使って表示する行を条件で絞り込み、その後にステータスバーで合計値を確認する......といった方法もありますが、毎回操作するのは面倒です。そこで活用したいのがSUMIFS関数、ということになります。

SUMIFS関数を使った操作の全体的な流れを知りたい方は、以下の動画をご覧ください。SUMIFS関数の構文と引数、詳しい入力方法や条件指定のバリエーションについては、その下で解説しています。

SUMIFS関数の構文

それでは、SUMIFS関数の構文を確認していきましょう。

---

=SUMIFS（合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...）

1. ［合計対象範囲］......合計する値の入力されたセル範囲を指定します。
2. ［条件範囲］......［条件］の検索対象とするセル範囲を指定します。
3. ［条件］......［条件範囲］を検索する条件を指定します。

---

SUMIFS関数を入力するときは、合計するセル範囲を第1引数［合計対象範囲］に指定するのがポイントです。続いて、［条件範囲］と［条件］をセットとして、複数の条件を指定します（最大127組）。関数を入力したときに表示される、引数の範囲を確認すると理解しやすいと思います。

最初に［合計対象範囲］に合計するセル範囲を指定します。続けて［条件範囲］と［条件］をセットで指定します。

SUMIFS関数は「AかつB」のようなAND条件しか指定できないと思われがちですが、「AまたはB」のOR条件も指定可能です。

なお、条件は1つでも構わないので、1つの条件を指定して数値を合計するSUMIF（サム・イフ）関数の代わりに利用することもできます。

2つの条件に一致する数値を合計する

SUMIFS関数のもっとも基本的な活用例を見ていきます。冒頭で紹介した表を例に、『開催地が「神田」かつ「オンライン公開」がない』という2つの条件を満たす参加者数を求めます。

「○○でない」の条件には、比較演算子の「<>」を利用します。引数［条件］で利用できる演算子については、本記事末尾のHINTを参照してください。

数式中に条件を記述する場合は「""」（ダブルクォーテーション）で囲んで指定しますが、実務では条件となる文字列や数値が別のセルに入力されていることが多いでしょう。ここでは、セルに入力した条件を参照します。合計するのは参加者数なので、第1引数［合計対象範囲］は「F2:F26」となり、完成した数式は以下の通りとなります。

  =SUMIFS(F2:F26,C2:C26,H2,D2:D26,I2)
  

1SUMIFS関数を入力する

セルH2とI2には条件を入力してあります。SUMIFS関数の結果を表示したいセル（ここではセルH5）に「=SUMIFS(F2:F26,C2:C26,H2,D2:D26,I2)」と入力します。

2結果が表示された

『開催地が「神田」かつ「オンライン公開」がない』の条件に一致する参加者数の合計が求められました。

指定した範囲内にある数値を合計する

続いて『参加費が「2,000円」以上かつ「3,000円」未満』の参加者数を求めてみます。2,000円以上は「>=2000」、3,000円未満は「<3000」で表現できます。条件は先ほどと同じように、セルH2とI2に入力してあります。

完成した数式は以下の通りです。［参加費］列（セルE2～E26）は2回参照することになります。

  =SUMIFS(F2:F26,E2:E26,H2,E2:E26,I2)
  

セルH2とI2に条件を入力してあります。『参加費が「2,000円」以上かつ「3,000円」未満』という条件なので、［参加費］列（セルE2～E26）は2回参照することになります。入力した数式は「=SUMIFS(F2:F26,E2:E26,H2,E2:E26,I2)」です。

なお、「<=yyyy/mm/dd"」のように指定して、日付を比較することもできます。

「または」（OR条件）で合計する

ここまでに見た2つの例はいずれも「AかつB」のAND条件でしたが、今度は「AまたはB」のOR条件で数値を合計してみましょう。ここでは『開催地が「新宿」または「池袋」』の条件を考えます。

しかし、単純に以下のような数式にすると、結果は「0」となってしまいます。

  =SUMIFS(F2:F26,C2:C26,"新宿",C2:C26,"池袋")
  

セルH2とI2には条件を入力してあります。入力した数式は「=SUMIFS(F2:F26,C2:C26,H2,C2:C26,I2)」ですが、結果は「0」となってしまいました。

SUMIFS関数の引数［条件範囲］と［条件］を複数指定した場合、条件は「AかつB」のAND条件となります。数式に指定した条件は『セルE2～E26の範囲で「新宿」』かつ『セルE2～E26の範囲で「池袋」』となり、この条件を満たすデータは存在しないので、結果が「0」となったわけです。

意図する条件は『「新宿」もしくは「池袋」』なので、2つの条件に一致するデータの合計をそれぞれSUMIFS関数で求めて足し合わせます。正しい数式は以下の通りです。

=SUMIFS(F2:F26,C2:C26,"新宿")+SUMIFS(F2:F26,C2:C26,"池袋")

セルH2とI2に条件を入力してあります。入力した数式は「=SUMIFS(F2:F26,C2:C26,H2)+SUMIFS(F2:F26,C2:C26,I2)」です。結果は「1491」となりました。

検算のため、フィルター機能で「新宿」と「池袋」に絞り込んでからステータスバーで合計を確認してみましょう。

［開催地］列で「新宿」と「池袋」に絞り込みました。参加者数をステータスバーで確認すると「1491」と表示され、SUMIF関数の結果と同じになりました。

「かつ」と「または」を組み合わせて合計する

最後に、「かつ」と「または」を組み合わせて合計する方法を紹介します。『参加費が「3000円」以上かつ開催地が「新橋」または「神田」』という条件は、どのように指定すればいいでしょうか？

一見難しそうではありますが、この条件は『参加費が「3000円」以上かつ開催地が「新橋」』または『参加費が「3000円」以上かつ開催地が「神田」』と同じ意味です。

つまり、参加費が3,000円以上（>=3000）の条件は共通で、［開催地］列の条件が「新橋」と「神田」になります。先ほどのOR条件と同じと考えると、数式は以下のようになります。

  =SUMIFS(F2:F26,E2:E26,">=3000",C2:C26,"新橋")+SUMIFS(F2:F26,E2:E26,">=3000",C2:C26,"神田")
  

セルH2、I2、J2に条件を入力してあります。入力した数式は「=SUMIFS(F2:F26,E2:E26,H2,C2:C26,I2)+SUMIFS(F2:F26,E2:E26,H2,C2:C26,J2)」です。結果は「1239」となりました。

検算のため、フィルター機能で絞り込んでステータスバーの合計を確認してみます。

［参加費］列で「3,000円」以上、［開催地］列で「新橋」と「神田」に絞り込みました。参加者数をステータスバーで確認すると「1239」と表示され、SUMIF関数の結果と同じになりました。

関連記事

• 連載「エクセル時短」の注目・新着記事一覧