SUMIFS関数の基本的な使い方と便利な活用方法

Microsoft Office エクセル(Microsoft Office Excel)

【Excel】SUMIFS関数の基本的な使い方と便利な活用方法

日々の業務で売上や契約金額データなどから対象のデータのみ金額や数量を集計するシーンは良くあるかと思います。

単純な集計であればSum関数を使えば集計できますし、ある条件を一つ満たすデータのみを集計する際はSumif関数で対応ができます。

さて、より集計の条件が複雑になった時はどうでしょうか。

例えば、条件Aと条件Bには合致していて、条件Cには合致しないデータを集計するといった場合などです。

今回は複数条件での集計業務に対応するSumifs関数について、基本的な使い方から応用編まで詳しく解説していきます。

Sumifs関数の書式

Sumifs関数の書式は以下のとおりです。

=SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)

引数名説明
合計対象範囲    (必須)合計するセルの範囲を指定します。
条件範囲 1    (必須)検索したい条件が含まれている範囲を指定します。
条件 1    (必須)検索したい条件を指定します。 条件は、32">32"B4"リンゴ"、または "32" のように入力することができます。
条件範囲 2, 条件 2, ...    (任意)追加の範囲と対応する条件。
条件は最大127個まで指定することができます。
Sumifs関数の書式

合計対象範囲に数値として入力されているもののみ集計されます。
文字列などがある際はエラーにはならずスルーされ数値のみ集計されますが、「#NAME?」や「#VALUE」などのエラー値があると計算結果もエラーとなるので注意が必要です。

Sumif関数の基本的な使い方

それでは実際に使用してみます。

サンプルデータとして日付、期、グループ、金額のデータを用意しました。

「上期」かつ「グループB」の合計金額を集計していきます。

Sumif関数の基本的な使い方
Sumif関数の基本的な使い方

まず合計対象範囲は金額が記載されている範囲「E3:E21」を指定します。

次に条件1の「期」を検索する対象範囲「C3:C21」を指定します。
条件1で「上期」を指定したいので「”上期”」とダブルクォーテーションで囲み検索する文字列を指定します。

最後に条件2の「グループ」を検索する対象範囲「D3:D21」を指定し、条件2で「B」を指定したいので「”B”」と同じく検索する文字列を指定します。

合計対象範囲と各検索対象範囲の行数は一致させてください。(上記例では3~21行目となっています)

Sumif関数の基本的な使い方
Sumif関数の基本的な使い方

55,561と計算結果が表示されました。

Sumif関数の応用編

分析データなどを作成するときはこのやり方を多用しますので、こちらも合わせて抑えていきましょう。

応用として「文字列を含む」という条件と日付や数値で「〇以上、かつ、△以下」という範囲指定条件について解説していきます。

まずは文字列についてです。
条件指定の際にワイルドカード「*」を使用します。

基本的に「●●を含む」「●●から始まる」「●●で終わる」の3パターンを押さえておくと良いでしょう。

「●●を含む」といった条件の場合

「●●を含む」といった条件の場合
「●●を含む」といった条件の場合

検索条件に「”*●●*”」のように記載します。

図の例では検索条件2に「"*cp*"」と記載しており、前後に何があっても「cpを含む製品コード」を2つ目の検索条件に指定しています。

このように検索したいワードをワイルドカード(*)で挟んで前後あいまい検索することがポイントです。

「●●から始まる」という条件の場合

「●●から始まる」という条件の場合
「●●から始まる」という条件の場合

条件に「”●●*”」のように記載します。先頭文字が決まっているので、後方のみワイルドカードを記載するのがポイントです。

図の例では「CT*」と記載しており、後方あいまい検索していくことで集計できます。

「●●で終わる」という条件の場合

「●●で終わる」という条件の場合
「●●で終わる」という条件の場合

条件に「”*●●”」のように記載します。語尾が決まっているので前方にワイルドカードを記載するのがポイントです。

図の例では「"*_c"」と記載し、前方あいまい検索で集計しています。

上級者編 日付や数値で範囲を指定して集計する方法

日付で期間指定をして集計するといったパターンはよくあるのでどのように設定するか解説していきます。

ワイルドカードを使ったあいまい検索よりも式が長くなってきますが、覚えると使い勝手は非常に良いです。

以下の例では、2021/04/01~2021/09/30の期間の金額を集計しています。

上級者編 日付や数値で範囲を指定して集計する方法
上級者編 日付や数値で範囲を指定して集計する方法

 

まず、合計対象範囲はこれまで同様に「E3:E21」を指定します。

続いて条件1と2に2021/04/01以降、2021/09/30までといった条件を指定していきます。
条件対象範囲は日付の列を指定し、検索対象の指定は「”>=2021/04/01”」、「”<=2021/09/30”」とします。

文字列内に「>=」や「<=」といった不等号を入れて指定日と結合するところがポイントです。

数値の上限・下限設定をして集計する際も同様に不等号を使用します。

Sumifの検索条件でセルを参照する

ここまでは検索条件をダブルクォーテーション「””」で囲んで直接入力していましたが、セル参照とすることもできます。

Sumifの検索条件でセルを参照する
Sumifの検索条件でセルを参照する

図のように集計元のデータ(B2:E21)と、集計先の表(H3:I6)があり、製品グループ別に集計をしていきます。

合計対象範囲はこれまでと同様ですが、条件指定1の部分で期間は「上期」としたいので、I2セルを選択しています。

このようにセルを参照することで、下期を集計したい場合はI2セルの中身を「下期」に変更するだけで集計してくれます。

条件指定2の部分では、表の製品グループの文字列「AT」~「CT」を含むで指定したいので、セル指定した後にアンパサンド「&」でワイルドカード「”*”」を結合しています。

「AT」の金額集計の関数が組み終わったら、「BT」「CT」の行に関数をコピペして集計データを完成させたいところですが、絶対参照「$」を付けないとコピペした際に集計対象範囲と条件範囲がズレてしまい正しく集計できないので注意が必要です。

Sumif関数のまとめ

今回は複数条件での集計業務に対応するSumifs関数について、基本的な使い方から応用編まで詳しく解説しました。

SumやSumifでは対応しきれない集計もSumifs関数を駆使すれば可能です。

様々な条件指定の方法を知っておくと、いざ集計関数をくみ上げるときにどうやったら求めるデータが算出できるか工夫できるようになってくるかと思います。
ぜひマスターして、業務効率化を図っていきましょう。

最後までご覧いただきありがとうございました。
情シスの自由帳は、情シスマンが半径3m以内のITに関する情報を掲載してるサイトです。

Windows系を主として、ソフトや周辺機器について思い立った物を色々解説しています。

操作環境
Windows11 Home
Microsoft Office 2019

エクセルに関するその他の記事

エクセルに関するその他の記事はこちらです。ぜひご覧ください。

エクセルで日付から曜日を表示させる方法
xls(古いエクセル)からxlsx(新しいエクセル)に変換する方法
エクセル 西暦から和暦にセルの表示を切り替える方法
エクセル 誕生日から年齢を自動計算する方法
エクセル(Excel)で作成したデータをワード(Word)に貼りつける方法7選

-Microsoft Office, エクセル(Microsoft Office Excel)
-,