thumb_sumifstoha_01

SUMIFS関数とは

SUMIFS関数は、Excelで使用できる関数の1つです。複数の条件に一致するセルを検索し、指定した範囲内の数値を合計することができます。

SUMIFSでは複雑な条件を指定して値の合計を得ることができ、さまざまな業務データや取引データの集計でよく使用されます。

例えば、顧客データから特定の地域ごとの顧客数を自動で集計したり、売上明細から特定の取引先ごとの売上額を自動で集計したりできます。

この記事では、Excelでのデータ集計をより便利に行いたい方のために、SUMIFS関数の基本的な使い方や具体的な活用例と、集計がうまくいかない場合の対処法などをわかりやすく解説していきます。

SUMIFS関数の基礎知識

まず、SUMIFS関数の概要や構文などの基礎について説明します。また、SUMIF関数との違いや、SUMIFS関数を使用する際の注意点も確認しましょう。

SUMIFS関数の概要

SUMIFS関数は、複数の条件に合致するセルの値を合計する関数です。

SUMIFSを使えば複数の列から条件を絞り込んで集計することができるため、さまざまな用途の集計に活用することができます。

例えば、全売上の中から特定の「地域」と「営業担当者」のように複数の条件を組み合わせて、特定の地域における担当者別の売上合計を求めることができます。

また、条件に不等式や部分一致を使うことで、より複雑なデータ集計に活用することもできます。

SUMIFS関数の構文

それでは、SUMIFSの使い方を見ていきましょう。SUMIFSの構文は、次の通りです。

■SUMIFSの構文

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

img_sumifstoha_03

【図】：　SUMIFS関数の構文　

■合計対象範囲
合計したい数値が入力されているセル範囲を指定します。この範囲にある、条件に合致したセルに対応する数値が合計されます。

■条件範囲1
検索対象とするセル範囲を指定します。ここで指定した範囲で、条件を満たすデータが集計の対象になります。

■条件1
条件範囲から、合計対象を絞り込むための条件を指定します。数値、文字列、比較演算子などを使って指定できます。

■条件範囲2
2つめの条件範囲です。条件範囲1と同じく指定できます。

■条件2
2つめの条件です。条件1と同じく指定できます。

以降、同様に「条件範囲」と「条件」をセットにして追加することができます。最大127個の条件まで設定が可能です。条件は1つでも問題ありません。

SUMIFSとSUMIFの違い

SUMIFSと似た関数に「SUMIF関数」があります。SUMIF関数も条件に基づいて指定した範囲内の数値を合計することができます。

SUMIFとSUMIFSの違いは、指定できる条件の数です。SUMIFでは条件を1つだけ指定することができます。SUMIF関数の構文は、次の通りです。

■SUMIFの構文

=SUMIF(範囲, 検索条件, 合計範囲)

■範囲
検索条件を探す対象のセル範囲です。

■検索条件
合計するデータを選び出すための条件を指定します。

■合計範囲
合計したい数値が入力されている範囲を指定します。

SUMIFとSUMIFSでは、条件の個数の違いのほか、引数の順序が異なることに注意が必要です。例えば、SUMIFでは合計範囲が第3引数ですが、SUMIFSでは第1引数です。

そのため、SUMIFとSUMIFSを併用したり使い分けたりする場合は、引数の順序を混同しないように注意が必要です。

SUMIFS関数の注意点

SUMIFS関数を使用する際、いくつかのポイントに注意する必要があります。

まず、合計対象範囲と条件範囲の行数や列数が一致しなければなりません。一致していないと正しい集計が行われませんので、しっかりと合わせましょう。

また、SUMIFSの引数で指定する複数の条件は、すべてAND条件として処理されます。つまり、指定したすべての条件に一致したセルのみが、合計対象範囲から集計されます。

そして、条件として文字列を指定する場合は、ダブルクォーテーション（"）で前後を囲む必要があります。例えば、特定の文字列を条件に設定する場合、「"りんご"」のように記述します。

SUMIFS関数の基本的な使い方

ここでは、SUMIFS関数を使った基本的な集計方法について説明します。具体的な例を挙げながら、1つの条件での集計、複数条件での集計、そしてクロス集計の方法について解説していきます。

1つの条件で集計する方法

まず、1つの条件だけで集計する方法から見ていきましょう。例えば、商品の売り上げデータの中で、商品が「パン」であるデータの売上合計を求めたいとします。

img_sumifstoha_05

【図】：　「パン」の売上を合計する　

商品が「パン」だけの売上の合計を求める場合、SUMIFSを使って「=SUMIFS(D2:D6,B2:B6,"パン")」の式を入力します。

引数は、「D2:D6」が合計を求めたい売上金額の範囲、「B2:B6」が、条件で検索する商品のある範囲、「"パン"」が合計する商品の条件を意味します。

複数の条件で集計する方法

では、SUMIFSで複数の条件を満たすセルの合計を求める例を見てみましょう。今度は、「東京」で販売された「パン」の売上合計を求めてみます。

img_sumifstoha_06

【図】：　「東京」で売れた「パン」の売上を合計する　

使用するSUMIFSの式は「=SUMIFS(D2:D6,B2:B6,"パン",C2:C6, "東京")」となります。

1つめの条件として「B2:B6」の範囲で「パン」であるデータ、2つめの条件として「C2:C6」の範囲で「東京」であるデータが指定され、両方を満たす売上金額だけが合計されます。

2つの条件でクロス集計する方法

SUMIFSで2つの条件を指定して、クロス集計を行うことができます。クロス集計とは、行と列にそれぞれ異なる項目を配置し、その交差する部分で結果を分析する方法です。

SUMIFSでクロス集計する例として、東京と大阪、パンと牛乳の売り上げ金額を、クロス集計で別表にまとめてみましょう。

img_sumifstoha_07

【図】：　地域と商品でクロス集計する　

売上一覧表の隣に、集計したい項目を行列として持つ表を作成します。左上のセルに「=SUMIFS($D$2:$D$9,$B$2:$B$9,G$1,$C$2:$C$9,$F2)」を入力して、各行列の見出しの値を条件にしてSUMIFSを入力します。

合計対象範囲と条件範囲は固定したいので「$」を付けて絶対参照にします。条件は、クロス集計表の各行列の見出しの位置を相対的に取得したいので、条件1は「G$1」、条件2は「$F2」としています。

左上のセルを他のセルにコピー＆ペーストすると、それぞれの各行列の見出しの値を条件としたSUMIFSが入力されて、クロス集計表が出来上がります。

SUMIFS関数の応用

SUMIFS関数を応用することで、さまざまな条件の集計を行うことができます。ここでは、不等号を使った条件指定や、文字列の部分一致による条件設定、さらにOR条件を利用した集計方法について解説します。

不等号を使った条件指定

SUMIFS関数では、不等号を使用して、数値データに対して特定の範囲や条件を指定した集計が可能です。

例えば、「売上が500円以上1,000円未満」の商品だけを合計したい場合、不等号を使って2つの条件を組み合わせます。

img_sumifstoha_09

【図】：　不等号を使った条件で合計を求める　

「=SUMIFS(C2:C16,C2:C16, ">=500", C2:C16, "<1000")」の数式で、売上が500円以上かつ1,000円未満のデータを集計できます。

また、日付にも不等号を使った条件指定が可能です。例えば、「2024年1月1日以降の売上」を集計する場合は、「=SUMIFS(C2:C16, A2:A16, ">=2024/1/1")」の数式を使って、日付範囲内で2024年1月1日以降の日付に該当する売上データを合計します。

部分一致を使った条件指定

ExcelのSUMIFS関数では、アスタリスク（\）を使って部分一致の条件を指定できます。これにより、特定の文字列を含むデータを集計することができます。*

例えば、商品リストの中で「りんご」を含む商品の売上を合計したい場合、「=SUMIFS(売上範囲, 商品名範囲, "\りんご\")」のようにアスタリスクを使用します。

img_sumifstoha_10

【図】：　「りんご」を含む商品の売り上げを合計する

この式では、商品名に「りんご」を含むすべての商品（例：「青森りんごジュース」や「りんごパイ」）の売上を合計します。この前後のアスタリスク（\*）は、前後に任意の文字列があることを示します。

「=SUMIFS(B2:B9, A2:A9, "りんご\*")」とすれば、「りんごパイ」や「りんごタルト」など、商品名が「りんご」で始まる商品の売上を合計できます。

また、「=SUMIFS(B2:B9, A2:A9, "\*りんご")」とすれば、「青森りんご」や「フルーツりんご」など、商品名が「りんご」で終わる商品の売上を合計します。

OR条件で集計する

SUMIFSで指定する複数条件はAND条件になるため、OR条件を用いてデータを集計したい場合、SUMIFS関数のみでは直接できません。

OR条件で合計を行いたい場合は、SUMIFS関数を複数回使用してその結果を足し合わせることで実現できます。

例えば、商品カテゴリが「パン」または「菓子」の売上データを集計したい場合、「=SUMIFS(C2:C11, B2:B11, "パン") + SUMIFS(C2:C11, B2:B11, "菓子")」のようにSUMIFS関数を2回使用し、それぞれの条件に対応する合計を計算します。

img_sumifstoha_11

【図】：　OR条件で合計する　

この式では、まず商品カテゴリが「パン」のデータの合計、次に「菓子」のデータの合計を求め、その2つの結果を足し合わせることで、「パン」または「菓子」であるすべてのデータを合計することができ、OR条件での集計を行っています。

他にも条件を増やしたい場合は、同様にSUMIFS関数を追加して結果を合計すれば対応できます。

SUMIFSでの集計がうまくいかない場合

SUMIFS関数を使用する際に集計結果が期待通りに表示されない場合があります。ここでは、SUMIFSの集計がうまくいかない2つのケースの原因や対処方法を説明します。

結果に「0」が表示される

SUMIFS関数では、条件として指定する文字列の条件がダブルクォーテーションで正しく囲まれていないと、結果が「0」と表示されてしまうことがあります。

img_sumifstoha_13

【図】：　文字列を含む条件はダブルクォーテーションで囲む　

例えば、上の図で「=SUMIFS(C2:C16, B2:B16, 牛乳)」の式は、文字列がダブルクォーテーションで囲まれていないため、結果が「0」になります。

この場合、「=SUMIFS(C2:C16, B2:B16, "牛乳")」と文字列をダブルクォーテーションで囲むことで、正確な集計ができます。

合計が「0」だとすぐ誤りに気付くケースも多いと思われますが、合計が「0」でもおかしくない集計を行う場合は、SUMIFSの誤りで「0」になっていることを見逃しやすくなります。

SUMIFSの条件の文字列には、ダブルクォーテーションを忘れないように十分気を付けましょう。

期待した通りの結果が表示されない

SUMIFS関数を使用して集計する際、集計対象の範囲にエラーが含まれていると合計結果がエラーになります。

例えば、各タスクの費用と、完了状況を記録するプロジェクトの進捗管理シートがあるとします。完了しているタスクの合計費用を「=SUMIFS(B2:B6, C2:C6, "完了")」の式で合計します。

img_sumifstoha_14

【図】：　合計範囲にエラーがあるとSUMIFSもエラーになる　

しかし、この例では「費用」列で、別の表から集計するために使用した関数で「＃NAME?」エラーが出ています。このままSUMIFS関数で費用の合計を計算してもエラーになります。

この場合は、SUMIFSの入力セルにはエラーが表示されるので分かりやすいです。合計対象範囲のデータがエラーを改善すれば、正しい合計が集計されます。

SUMIFS関数を活用してより柔軟な集計を行おう

ここまで、SUMIFS関数の概要や使い方の具体例、集計がうまくいかない場合の対処法などについて説明しました。SUMIFSを使えば、複数の条件に基づいて集計をより便利に行えることが分かりました。

SUMIFSを活用することで、手作業で集計を行うよりもミスを減らし、素早く集計することができます。また、不等号や部分一致、OR条件を応用することで、より複雑なデータ集計も可能です。

日常の業務のデータ集計において、ぜひSUMIFSを使って業務の効率化を図ってみてください。