Excelの表の中から、「特定の条件を満たす行の数だけ合計したい」など、ある条件を満たす行だけ「sum」を行いたい場合は、「SUMIF」関数が使えます。

このページでは、SUMIF関数、および派生形としてSUMIFS関数の使い方を説明します。

SUMIF関数はこのようなときに使う

sumif関数はこのようなときに使おう

今回は、このような表を例にして説明します。「それぞれの果物の購入金額の合計」を知りたいものとします。

単純に、「りんごの購入金額合計」「みかんの購入金額合計」を知りたいなら、フィルター機能を使えばOKです。

フィルター機能で条件を満たす行だけ合計する

この状態で、E列の「金額を合計したい範囲」のセルを選択します。

金額を合計したい範囲のセルを選択

すると、画面の右下に合計金額が表示されています。

セルの選択範囲の合計は、Excelの右下に表示されている

ただし、数えたいものはほかに「みかん」「ぶどう」などたくさんあるので、その場合はフィルター機能を使う方法では不便です。

SUMIF関数を使えば、このように表でまとめて表すことができます。

SUMIF関数で、各条件を満たす値の合計を表で表す

SUMIF関数の使い方

SUMIF関数は、このように書きます。

=SUMIF(合計したい対象の列, “条件”, 合計する列)

以下の表の例で、”りんご”の列の金額を合計したい場合は、=SUMIF(H:H,A2,J:J)

SUMIF関数の使い方

※以下の説明は、若干「COUNTIF関数の使い方」と重複している箇所があります。「条件」の部分までは、COUNTIFの使い方と同じなので、合わせて覚えてください。ExcelのCOUNTIF関数で、条件に当てはまる行の数を数えられる!

まずは、「りんごを買った回数」を数えます。言い換えると、右側の表のうち、「りんご」が表示されている行の数を数える、ということです。

「りんご」はH列にあるので、今回の「個数の対象を数えたい列」はH列になります。「H列が条件に当てはまるかどうかを見る」、ということです。

「H2:H18」と指定してあげてもいいですが、面倒ならば「H:H」でOKです。

次に、「個数の対象を数えたい列(今回はH列)」が満たすべき条件を設定します。今回満たすべき値は”りんご”なので、条件部分は”りんご”とします。

条件部分は「セルの値の参照」でもOKです。”りんご”と書くよりは、りんごという値が入っているセル(今回の場合はA2セル)をしていしてあげると楽です。

「合計する列」は、COUNTIFにはない、SUMIF特有のものです。「数字が含まれている、足し算する列」を指定します。なお、この際、「りんご」や「みかん」などの条件にかかわらず、数字が含まれている列すべてを指定します。

今回の場合、合計したい「金額」が含まれている列はJ列なので、J:Jを指定します。

条件部分を相対参照にすると、コピペするだけでほかの果物もすべて合計できます。

SUMIF関数で、各条件を満たす値の合計を表で表す

みかん:=SUMIF(H:H,A3,J:J)

バナナ:=SUMIF(H:H,A4,J:J)

ぶどう:=SUMIF(H:H,A5,J:J)

相対参照、絶対参照はこちら

SUMIFS関数の使い方

SUMIF関数もCOUNTIF関数と同様、「条件は1つしか指定できない」という欠点があります。例えば、「りんごの購入金額」と「4月の購入金額」を同時に指定して、「りんごを4月に購入した合計金額」を数えることは、SUMIF関数ではできません。

この欠点を解消して、同時に複数の条件を設定できるようになった関数が、SUMIFS関数です。

※名称の違いは、後ろにSがついているかどうかしか違いがありません。

関数の書き方は以下の通りです。

=SUMIFS(合計する列, 合計したい対象の列1, “条件1”, 合計したい対象の列2, “条件2”, …)

好きな数だけ条件を設定できます。もちろん1つだけでもOKです。

以下の表の例で、”4月に購入した”、”りんご”をの合計金額を表示したい場合は、=SUMIFS(J:J,H:H,A2,F:F,”4″)

sumifs関数の使い方

H:H,A2の部分はSUMIFと同様です。「H列がりんごであること」を条件にします。“りんご”と直接入力するのではなく、セルを相対参照で参照して、みかんやバナナの列にコピペします。

F:F,”4″の部分は、「購入月が4月であること」を表しています。右側の表で、月はF列に記載されています。「F列が4月である」という条件を設定します。

なお、SUMIFSの条件は、すべて満たす必要があります。「条件1もしくは条件2のどちらかを満たす」という設定はできません。

また、SUMIFS関数は、「合計する列を先頭に書く」ということにも注意してください。合計する列(金額・J:J列)は、SUMIF関数では関数の末尾に記載していましたが、SUMIFS関数では、関数の先頭に記載します。

SUMIF, SUMIFS関数の注意点

SUMIF関数、SUMIF関数の注意点を2つ紹介します。

SUMIF関数、SUMIFS関数の注意点

  • 「~以上(以下/未満/より大きい)」の表し方
  • 条件列の指定方法

※なお、SUMIF, SUMIFS関数の注意点は、COUNTIF(S)関数の注意点と全く同じなので、こちらも合わせて参考にしてみてください。ExcelのCOUNTIF関数で、条件に当てはまる行の数を数えられる!

SUMIF, SUMIFS関数における、「~以上(以下/未満/より小さい)」の表し方

「~以上(以下/未満/より小さい)」の表し方(SUMIF, SUMIFS共通)

SUMIF、SUMIFS関数の条件には、「一致する」だけではなく、「~以上(以下/未満/より小さい)」を設定することもできます。

SUMIF関数の条件設定

方法は、COUNITF関数のときと同じです。

設定の仕方は次の通りです。セルを参照しない場合(数字を直接書く場合)と、セルを参照する場合で書き方が若干異なります。

・数字を直接書く場合:

不等号と数字をダブルクォーテーションで囲む。

例:「F列が4以上」と設定する場合
F:F, “>=4”

同様に、「4以下」「4より大きい」「4未満」の表し方は次の通りです。

      • 4以下:”<=4″
      • 4より大きい:”>4″
      • 4未満:”<4″

・数字を、他のセルから参照する場合:

不等号のみダブルクォーテーションで囲み、ダブルクォーテーションで囲んだ不等号と、セル番号を&でつなげる。

例:「F列が『A2の値以上』と設定する場合」
F:F, “>=”&A2

同様に、「以下」「より大きい」「未満」の表し方は次の通りです。

      • A2の値以下:”<=”&A2
      • A2の値より大きい:”>”&A2
      • A2の値未満:”<“&A2

なお、SUMIF、SUMIFS関数においても、「○○以上××以下」を1つの条件で設定することはできません。

「F列が4以上8以下」を設定したい場合は、「=SUMIFS(F:F, “>=4”, F:F, “<=8”)」と2つの条件に分けて設定する必要があります。

SUMIF, SUMIFS関数でエラーが起こる場合/計算結果が合わない場合は、条件列の見直しを!

下の表では、C2セルにおいてSUMIFS関数のエラーが発生しています。

SUMIFS関数のエラー

参照範囲は一見問題ないように見えますが、エラーになっている原因は、「=SUMIFS(J2:J18,H:H,A2,F:F,”4″)」と、1か所だけ参照範囲列の長さが異なることです。

この場合は、「=SUMIFS(J:J,H:H,A2,F:F,”4″)」もしくは「=SUMIFS(J2:J18,H2:H18,A2,F2:F18,”4″)」と修正すると、エラーが解消されます。

SUMIFS関数のエラーを解消

また、SUMIF関数においても同様の事象が起こります。「条件の列」と「合計の列」の長さが合っていないと、合計を正しく計算してくれません。

SUMIF関数の計算結果が合わない

合計金額が1,240と、実際の結果と合っていません(実際は2,320)

こちらの原因も、「=SUMIF(H2:H18,A2,J:J)」と参照範囲列の長さが異なるためです。「=SUMIF(H:H,A2,J:J)」にすると解決します。

SUMIFS関数と異なりエラーが表示されていないので、一見問題ないように見えますが、実際は計算結果が合わないことになります。実際にSUMIF関数を使用する場合はご注意ください。