EXCELの SUMIFS関数 で複数条件を満たす値の合計を求める

2018/08/07






今回は SUMIFS関数 をピックアップします。
「特定の1条件に合致した値」を検索するときに便利な関数に VLOOKUP関数 があります。

また、「特定の1条件」に合致する複数のデータ(数値)の合計を求めるには SUMIF関数 があります。

では、「複数条件」を満たすデータの合計を求めるにはどうすればよいでしょうか?

そんな時に便利なのが SUMIFS関数 です。SUMIFではなく SUMIFS です。
例えば、顧客名簿の中で、住所が「東京都」かつ 性別が「女性」の顧客の「購入金額合計」を求める場合などです。

SUMIF関数 とは違う ので注意してください。
では、さっそく図解しながら説明していきます。


SUMIFS関数

SUMIFS関数とは

SUMIFS関数とは『複数の条件に一致する数値の合計を求める関数』で;

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

のように表されます。

条件1、条件2、条件3、と条件に上限がなく 対象となる数値の合計を求められるのが最大の特徴です。

SUMIFS関数の用途

SUMIFS関数について、サンプルを使って説明します。
まず、作表が完成した画像を見てみましょう。「集計エリア」が今回のキモです。

sumifs_1
A列からD列の表が「顧客名簿」のサンプルです。(たった10名の顧客名簿。。。)

そして、名簿の中で「性別が女性」(条件1)、「住所が東京」(条件2)を両方満たす顧客の「購入額の合計」を表示しているのがセルF6 の金額(12,680円)で、SUMIFS関数 を使用して求めています。

セルE4やF4の値を変更すると、そのたびに2つの条件に合致するデータの購入額の合計がセルF6に表示されるわけです。

SUMIFSの使い方

SUMIFS関数の使い方について、もう少し掘り下げます。

sumifs_2

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

この数式に当てはめて考えると、D列が「合計対象範囲」、B列が「条件範囲1」、セルF4が「条件1」、C列が「条件範囲2」、そしてセルG4が「条件2」に該当します。

上の図の、セルF6に入る実際の SUMIFS関数の使い方 は;

=SUMIFS(D3:D12, B3:B12, F4, C3:C12, G4 )

となります。理解できたでしょうか?

次は同じく SUMIFS関数 を使って別パターンでの複数条件を満たす値の合計を求めます。


不等号を使って「〇以上△未満」の値を合計する

SUMIFS と不等号

今度は、SUMIFS 関数に 不等号 を組み合わせて、「〇以上△未満」という条件を満たす数値の合計を求めるパターンも紹介しておきます。

最初に紹介した例は「複数列」の条件を満たすパターンでしたが、今度は「同一列の中の複数条件」を満たすパターンです。
今度も作表が完了した画像からお見せします。

sumifs_3
今度の顧客リストもさきほどのものと似ていますが、B列が「年齢」になっています。

そして、EとF列 が複数条件を満たす購入額合計を求める表です。
サンプルでは『顧客のうち、年齢が30歳以上、40歳未満の方の購入額合計』を求めています。

条件の項目は「年齢」だけですが、「30歳以上」「40歳未満」と同一列内で複数の条件が指定されています。
これも、VLOOKUP関数や SUMIF関数では対応が出来ず、SUMIFS関数 を使用します。


SUMIFS関数 に「不等号」を組み合わせる

今度も SUMIFS関数の使い方自体はほとんど変わっていません。
ですが、不等号を加えるというちょっとした工夫をしています。

SUMIFS関数は先ほども述べたとおり;

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

のように表されます。

今回の場合「条件範囲1」と「条件範囲2」は共通(=年齢)ですので、B3:B11 となります。

したがって、セルE6に入る数式は;

=SUMIFS(C3:C11, B3:B11, ">="& E4, B3:B11, "<"& F4

となるのですが、重要なポイントは「条件1」と「条件2」の前にそれぞれ「">="&」「"<"&」のように不等号が挿入されていて、「セルE4の値以上」「セルF4の値未満」という条件が記されていることです。

Point不等号は「文字列として」数式内に挿入されるため、不等号の前後はダブルクォーテーション「"」で挟み、「&」でセル番号につなぎます。

 

こうすることで、条件に合致した3件のデータ(下図の黄色セル)の購入額の合計が求められます。

sumifs_4
パターンを覚えてしまえばそれほど難しい関数ではないので、ぜひとも職場で活用してみてください。

関連記事



併せて読んでおくとさらに便利

COUNTIF で「を含む」データをカウントする|Excel 関数
COUNTIF関数・ワイルドカード
COUNTIF関数を使い「~を含む」データをカウントする方法を解説。ExcelのCOUNTIF関数で任意の文字列を含むセルをカウントする にはちょっとした工夫が必要なのです。ヒントは「ワイルドカード」。

日次の売上げを月別に集計する|SUMIF関数
集計・SUMIF関数
エクセルで月別に集計する方法です。SUMIF関数を使って、1年間の日次(日別)売上げが羅列されたデータから、月別に集計した売上表を作成してみました。

エクセルのフィルタで抽出した値の合計を表示する方法
フィルター・SUBTOTAL関数
エクセルのフィルタで抽出された値の合計を求める方法。列にフィルタがかかっているときは「フィルタで抽出された値の合計」を表示させる方法です。

EXCELでベスト5の順位表を作る方法 Part 1
LARGE関数・RANK関数
学校の成績ベスト5、〇〇の都道府県別ベスト10。。。 日々データ分析に携わっていると、仕事でも何かと「順位」「ベスト〇位」を求められることがしばしばです。


HLOOKUP関数とVLOOKUP関数の違いと使い方
HLOOKUP関数・VLOOKUP関数
「HLOOKUP関数」をご存知ですか? HLOOKUPとVLOOKUPとの違いについて解説します。HLOOKUPを使った複数条件に合致するデータの抽出方法や、HLOOKUPの[#N/A]エラーを非表示にする方法などなど。

※その他関連記事は、このページの下の方にもあります。

-EXCEL&VBA
-,

シェアしていただけると助かります。