SUMIF関数との第1引数の違いに注意する

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

最初にSUMIFS関数の構文を確認しておきましょう。

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

合計するセル範囲を第1引数［合計対象範囲］に指定するのがポイントです。引数［条件範囲］と［条件］をセットとして、複数の条件を最大127組まで指定できます。

SUMIF関数では第1引数［範囲］に検索条件とするセル範囲を指定するのに対して、SUMIFS関数では第1引数［合計対象範囲］に合計するセル範囲を指定します。間違えないように注意してください。

［条件範囲］と［条件］のセットで指定した条件は「AかつB」を満たすAND条件となりますが、少し工夫すれば「AまたはB」のOR条件も指定可能です。

以下のような複数の条件を指定して、SUMIFS関数の基本をマスターしましょう。

「かつ」と「または」を組み合わせる複雑な条件指定も可能です。

2つの条件に一致する場合に合計する

SUMIFS関数の基本です。ここでは2つの条件『開催地が「池袋」、かつ、参加費が「3,000円以上」』を指定します。合計するのは参加者数ですから、第1引数の［合計対象範囲］には「F8:F32」として、入力する数式は、以下の通りです。

=SUMIFS(F8:F32,E8:E32,"池袋",D8:D32,">=3000")

引数［条件］で利用できる演算子については、本記事末尾のHINTを参照してください。「3,000円以上」の条件は「">=3000"」と指定します。

1SUMIFS関数を入力する

セルE1に「=SUMIFS(F8:F32,E8:E32,"池袋",D8:D32,">=3000")」と入力します。

2結果が表示された

『開催地が「池袋」、かつ、参加費が「3,000円以上」』の条件に一致するデータの参加者数が合計されました。

条件をセル参照するように、数式の内容を変更してみましょう。セルJ1に「池袋」、セルK1に「>=3000」と入力してある場合の数式です。

=SUMIFS(F8:F32,E8:E32,J1,D8:D32,K1)

セル参照で条件を指定しても結果は同じ「566」となりました。

1つの条件に一致するデータを合計する

SUMIFS関数の引数［条件範囲］と［条件］のセットを1つのみ指定することも可能です。

例えば『オンライン公開が「－」』を条件とした場合、以下のような数式になります。「○○でない」の条件は、比較演算子を使って「<>」と指定します。

=SUMIFS(F8:F32,C8:C32,"<>－")

セルE2に「=SUMIFS(F8:F32,C8:C32,"<>－")」と入力した結果です。SUMTIF関数を使って「=SUMIF(C8:C32,"<>－",F8:F32)」としたときと同じです。

範囲内のデータを合計する

続いて「参加費が2,000円以上、3,500円未満」の条件を指定します。2,000円以上は「">=2000"」、3,500円未満は「"<3500"」で表現できます。［参加費］列は、2回参照することになります。

=SUMIFS(F7:F31,D7:D31,">=2000",D7:D31,"<3500")

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

セルE3に「=SUMIFS(F7:F31,D7:D31,">=2000",D7:D31,"<3500")」と入力した結果です。

OR条件で合計する

「AまたはB」のOR条件で数値を合計してみます。ここでは『開催地が「新橋」または「神田」』の条件を考えます。単純に考えると数式は、

=SUMIFS(F8:F32,E8:E32,"新橋",E8:E32,"神田")

のように思えますが、結果は以下の通り間違いです。「0」となってしまいました。

セルE4に「=SUMIFS(F8:F32,E8:E32,"新橋",E8:E32,"神田")」と入力した結果です。

よく考えれば分かりますが、『セルE8～E32の範囲で「新橋」』かつ『セルE8～E32の範囲で「神田」』というデータは存在しないので、合計も「0」となるわけです。求めたいのは「新橋」もしくは「神田」ですから、2つの条件に一致するデータの合計をそれぞれ求めて足し合わせます。

=SUMIFS(F8:F32,E8:E32,"新橋")+SUMIFS(F8:F32,E8:E32,"神田")

セルE4に「=SUMIFS(F8:F32,E8:E32,"新橋")+SUMIFS(F8:F32,E8:E32,"神田")」と入力した結果です。

複雑な条件を整理して合計する

最後に指定する条件は、『参加費が3,000円以上、かつ、開催地が「新橋」または「神田」』です。一見複雑そうですが、『参加費が3,000円以上、かつ、開催地が「新橋」』または『参加費が3,000円以上、かつ、開催地が「神田」』と同じ意味です。先ほどのOR条件と同じと考えると数式は以下のようになります。

=SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"新橋")+SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"神田")

セルE5に「=SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"新橋")+SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"神田")」と入力した結果です。

フィルターをかけて確認してみます。［参加費］列で3,000円以上、［開催地］列で「新橋」と「神田」に絞り込みました。

［参加費］列で3,000円以上、［開催地］列で「新橋」と「神田」にフィルターで絞り込みました。結果は「1152」で、先ほどの数式と同じ結果です。

この数式をもう少しスマートに整えるには、「配列」を使います。簡単な使い方を下のHINTで紹介しますので、もっと極めたい人は参考にしてください。

複数条件を指定して数値を合計するときは「SUMIFS」を使います。条件は1つだけの指定も可能なので、SUMIF関数は使わずにSUMIFS関数のみを使ってもいいでしょう。引数の順番の違いによる混乱も回避できます。

=SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"新橋")+SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,"神田")

=SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,{"新橋","神田"})

=SUM(SUMIFS(F8:F32,D8:D32,">=3000",E8:E32,{"新橋","神田"}))