Office Hack

ExcelのSUMIF関数の使い方|検索条件に一致するセルの値を合計する

  • by yoshihiro
  • Release
  • Update

ExcelのSUMIF(サムイフ)関数は、あらかじめ指定したセル範囲の中から条件が合致するデータを探し、その条件に合致した範囲のデータを合計する関数です。

以下の記事では、SUMIF関数の基本的な使い方をご紹介しています。

SUMIF関数とは?

SUMIF関数は、指定された検索条件に一致するセルの値を合計する関数です。

SUM関数は指定した範囲をすべて合計しますが、SUMIF関数では条件を指定することで合計する対象を絞ることができます。

SUMIF関数の書式

まずは、SUMIF関数の書式を確認していきましょう。

SUMIF関数の書式

SUMIF関数の書式は「=SUMIF(範囲,検索条件,[合計範囲])」のように記述します。SUMIF関数では引数を最大3つ使用します。下記で1つずつ説明していきます。

※引数(ひきすう)とは、関数が結果を返すために必要な材料です。関数名の後の括弧「()」内に入力します。

引数名 説明
範囲 (必須) 検索の対象となるセル範囲を指定します。
検索条件(必須)

「範囲」からセルを検索するための条件を数値、式、セル参照、文字列、関数の形式で指定します。

文字列を指定する場合は、「"(ダブルクォーテーション)」で囲みます。

合計範囲 合計の対象となるセル範囲を指定します。省略すると「範囲」の中から条件に一致したセルが合計の対象となります。文字列や空白は無視されます。

第1引数(範囲)

SUMIF関数の範囲

1番目の引数は「範囲」です。この引数は必須です。検索の対象となるセル範囲を指定します。

SUMIF関数の範囲

例えばバナナだけの合計価格を求めたい場合、バナナを含むC3からC9セルを指定します。

第2引数(検索条件)

SUMIF関数の検索条件

2番目の引数は「検索条件」です。この引数は必須です。「範囲」からセルを検索するための条件を数値、式、セル参照、文字列、関数の形式で指定します。文字列を指定する場合は、「"(ダブルクォーテーション)」で囲みます。

SUMIF関数の検索条件

例えばバナナだけの合計価格を求めたい場合、範囲である「C3:C9」から検索条件として「バナナ」を指定します。文字列なので「"(ダブルクォーテーション)」で囲っています。

検索条件では条件を指定する際に比較演算子という「>」や「=」を使用することができます。検索条件で使用できる比較演算子は下記の表をご覧ください。

条件式 意味 読み方
> 1 1よりも大きい 大なり(だいなり)
>= 1 1以上 大なりイコール
< 1 1よりも小さい 小なり(しょうなり)
<= 1 1以下 小なりイコール
= 1 1と等しい(1と同じ) 等号、イコール
<> 1 1と等しくない(1以外) 山カッコ、大なり小なり

検索条件ではワイルドカードを使用するとさらに複雑な検索もすることが可能です。検索条件で使用できるワイルドカードは下記の表をご覧ください。

パターン 意味 使用例
*
(アスタリスク)
1文字以上の任意の文字列 "A*"・・・「A」で始まる文字列
"*A*"・・・「A」を含む文字列
"*A"・・・「A」で終わる文字列
?
(疑問符)
任意の1文字 "A?"・・・「A」で始まる2文字
"?A?"・・・「A」を含む3文字
"?A"・・・「A」で終わる2文字
~
(チルダ)
ワイルドカードの「*」や「?」を検索する "A~?"・・・「A?」
"A~*"・・・「A*」

第3引数(合計範囲)

SUMIF関数の合計範囲

3番目の引数は「合計範囲」です。合計の対象となるセル範囲を指定します。省略すると「範囲」の中から条件に一致したセルが合計の対象となります。

SUMIF関数の合計範囲

例えばバナナだけの合計価格を求めたい場合、価格の範囲(D3:D9)を指定します。

SUMIF関数の使い方(実践)

実際にSUMIF関数を使用する例をご紹介します。引数をどのように使用するか試してみましょう。

作業時間:3分

  1. 関数名を入力する

    関数名を入力する

    SUMIF関数を入力したい【セル(例ではD10セル)】を選択し、『=SUMIF(』と入力します。

  2. 範囲を指定する

    範囲を指定する

    SUMIF関数の範囲を指定します。「=SUMIF(」に続いて『C3:C9,』と入力します。対象の範囲をマウスでドラッグして選択することもできます。

  3. 検索条件を指定する

    検索条件を指定する

    SUMIF関数の検索条件として「バナナ」を指定します。「=SUMIF(C3:C9,」に続いて『"バナナ",』と入力します。文字列なので「"(ダブルクォーテーション)」で囲みます。

  4. 合計範囲を指定する

    合計範囲を指定する

    SUMIF関数の合計範囲を指定します。「=SUMIF(C3:C9,"バナナ",」に続いて『D3:D9)』と入力し、【Enter】キーを押します。合計範囲もマウスでドラッグして選択することもできます。

  5. SUMIF関数の結果

    SUMIF関数の結果

    SUMIF関数の結果が数式を入力したセル(例ではD10セル)に表示されました。「バナナ」に対応した価格は「198」と「210」なのでその合計である「408」が計算されています。

さまざまな検索条件の指定方法

SUMIF関数の検索条件でよく使われる条件の指定方法をご紹介します。

空白以外

空白以外のセルを検索するには比較演算子の「<>」を使用します。

空白以外のセル

例では、種類の列で空白以外の価格を合計するとします。まずSUMIF関数を入力する【セル(例ではG3セル)】を選択します。

SUMIF関数の入力

セルに直接『=SUMIF(C3:C9,"<>",D3:D9)』と入力します。範囲に「C3:C9」、検索条件に「"<>"」、合計範囲に「D3:D9」をそれぞれ指定しています。

検索条件で「"<>"」を指定していますが、省略せずに表すと「"<>"&""」になります。空白("")と等しくない(<>)を文字列結合(&)していますので空白以外という条件を表します。

空白以外のセルの値を合計する

空白以外のセルの値を合計した結果がG3セルに表示されました。

特定の文字列を含む

特定の文字列を含むという条件を指定するには、ワイルドカードの「*(アスタリスク)」を使ってあいまい検索をします。

SUMIF関数で含む検索

例えば、例のように種類の列から「ん」を含むセルの値を合計するとします。G3セルを選択し、『=SUMIF(C3:C9,"*ん*",D3:D9)』と入力します。

検索条件で「"*ん*"」を指定していますが、対象の文字列「ん」の前後に「*(アスタリスク)」を記述することで「ん」を含む文字列を表します。

SUMIF関数の含む検索の結果

検索条件の「ん」を含む文字列であるセルの値だけが合計されてG3セルに表示されました。

特定の文字列を含まない

特定の文字列を含まないという条件を指定するには、ワイルドカードの「*(アスタリスク)」と比較演算子「<>」を使用します。

SUMIF関数のあいまい検索

例えば、例のように種類の列から「ん」を含まないセルの値を合計するとします。G3セルを選択し、『=SUMIF(C3:C9,"<>*ん*",D3:D9)』と入力します。

検索条件は対象の文字列「ん」の前後に「*(アスタリスク)」を記述することで「ん」を含む文字列を表していますが、比較演算子「<>」を使うことで否定しています。

SUMIF関数のあいまい検索の結果

検索条件の「ん」を含まない文字列であるセルの値だけが合計されてG3セルに表示されました。

日付の範囲(期間)

SUMIF関数の検索条件で日付の範囲を指定するには、比較演算子を使用します。直接日付を指定する方法とTODAY関数と組み合わせて今日以降の日付を指定する方法をご説明します。

日付の範囲

例えば、例のように日付の列から11月3日以降のセルの値を合計するとします。G3セルを選択し、『=SUMIF(C3:C9,">=2019/11/3",D3:D9)』と入力します。

検索条件で「">=2019/11/3"」を指定していますが、比較演算子「>=」を指定することで以上(以降)を表しています。

日付の範囲の結果

11月3日以降のセルの値を合計した結果がG3セルに表示されました。

今日以降の合計

例えば、例のように日付の列から今日(11月7日の場合)以降のセルの値を合計するとします。G3セルを選択し、『=SUMIF(C3:C9,">="&TODAY(),D3:D9)』と入力します。

検索条件で「">="&TODAY()」を指定していますが、TODAY関数で今日の日付を取得して比較演算子「>=」を指定することで今日以降を表しています。

今日以降の合計

今日(11月7日の場合)以降のセルの値を合計した結果がG3セルに表示されました。

複数条件に対応する方法(OR条件/AND条件)

SUMIF関数で複数条件に対応する方法をOR(または)条件、AND(かつ)条件のパターンに分けて以下の記事でご紹介しています。

AND条件ではSUMIF関数とAND関数を組み合わせた方法の他にSUMIFS関数の使い方もご紹介しています。

SUMIF関数の合計範囲を可変にする

合計したい範囲が月別などで切り替えることが多い場合、合計範囲を可変にする方法を説明します。

合計範囲を可変にする

例では、店舗ごとの月別の売上個数がデータとしてあります。別途右側の表で、H3セルに指定した月の店舗ごとの合計(I3~K3)を計算するとします。

合計範囲を可変にする

①関数を入力するセルを選択し、②数式バーに『=SUMIF($B$3:$B$9,I2,OFFSET($B$3:$B$9,,MATCH($H$3,$C$2:$F$2,0)))』と入力します。関数が入れ子になっており長い数式なので分解して見ていきましょう。

まずSUMIF関数は簡単に「=SUMIF($B$3:$B$9,I2,合計範囲)」と表せます。検索条件はA店を指す「I2セル」を指定し、合計範囲ではOFFSET関数とMATCH関数を組み合わせています。

OFFSET関数は、基準のセルから指定された行数と列数だけシフトした位置にあるセル範囲の参照を返す関数です。書式は「=OFFSET(基準, 行数, 列数, [高さ], [幅])」のように記述します。

MATCH関数は、範囲の中から指定した項目を検索し、その範囲内の相対的な位置を返す関数です。 書式は「=MATCH(検査値, 検査範囲, [照合の型])」のように記述します。

OFFSET関数の列数にMATCH関数を指定しています。MATCH関数でH3セルに書いてある月を検索値として、左側の表の月の見出し(C2:F2)から適合する位置を特定します。その特定した位置をOFFSET関数に指定することで、合計範囲を可変にしています。

合計範囲を可変にする結果

I3セル以外にも数式を反映させると5月の各店舗の売上個数が一覧で表示されました。

可変の結果を確認する

H3セルの月を『6月』にすると6月の各店舗の売上個数が一覧で表示されます。

VBAでSUMIF関数を使用するには

ExcelではSUMIF関数を使用することで検索条件に一致するセルの値を合計することができますが、VBA(マクロ)でもSumIfを使用すれば同じように条件によって合計することができます。

VBAのSumIfはワークシート関数(worksheetfunction)で使用できるメソッドの1つです。指定したセル範囲の数値を条件付きで合計します。

構文

式としては「WorksheetFunction.SumIf(範囲,検索条件,合計範囲)」のように記述します。

使用例

VBAのコードの中で「SumIf」がどのように使用されるのか使い方をご紹介します。

VBAのSUMIF

上図は種類ごとに価格が入力されています。C3~C9のセル範囲から検索対象の「バナナ」に一致したセルの値の合計をD10セルに出力させてみましょう。


Sub sample()

    Cells(10, 4) = WorksheetFunction.SumIf(Range("C3:C9"), "バナナ", Range("D3:D9"))

End Sub

VBAで「SumIf」を使ったサンプルになります。

  • 2行目:「Range(“C3:C9”)」が範囲です。C3~C9のセル範囲を検索範囲とします。
  • 2行目:「”バナナ”」が検索条件です。
  • 2行目:「Range(“D3:D9”)」が合計範囲です。範囲から検索条件に一致したセルの値を合計します。

SUMIF関数のよくあるエラー

数式は正しいのに計算結果が0になる

SUMIF関数で合計結果が0になってしまう現象があります。ほとんどの場合、合計範囲が数値ではなく文字列だったために0になってしまうことがあります。

数式が合っているのに結果が0になるときは合計範囲が数値かどうか確認しましょう。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください