オーロラさんの勉強帳

IT企業勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。少しでもお役に立てたら幸いです。

【Excel】SUMIFS関数の使い方 ~指定した複数条件に一致する行データの値を合計する~

本記事ではExcelSUMIFS関数の使い方を説明します。

SUMIFS関数とは。SUMIFS関数の書式

SUMIFS関数は、1つもしくは複数の検索条件に一致した『行』の数値データを合計する関数です。
もう少し詳細に書くと、指定した検索範囲の中で検索条件一致する行を検索し、その行の合計範囲の数値データを合計します。
複数の検索範囲・検索条件を指定する場合は、複数の検索範囲・検索条件に一致する行を検索し、その行の合計範囲の数値データを合計します。

検索条件には比較演算子、ワイルドカードを使うことができます。


SUMIFS関数の書式は以下の通りです。

SUMIFS関数の書式
=SUMIFS(合計範囲, 検索範囲1, 検索条件1[, 検索範囲2, 検索条件2,・・・])

引数合計範囲、検索範囲1、検索条件1は必須です。

SUMIF関数の書式は「=SUMIF(検索範囲, 検索条件, 合計範囲)」とSUMIFS関数と指定する順番が異なることに注意しましょう。
書式に迷うことがあれば、Googleで検索したり、数式バーに=SUMIF(、=SUMIFS(など関数を入力したりして確認しましょう。
f:id:auroralights:20211231002229p:plain


※検索範囲、合計範囲の注意点
検索範囲、合計範囲の開始行、終了行を必ず合わせます。例えば検索範囲が「A1:A10」の場合は合計範囲を「C1:C10」とします。(検索範囲、合計範囲ともに開始が1行目、終了が10行目)
もし、検索範囲「A1:A10」(開始行1:終了行10)、合計範囲「C5:C10」(開始行5:終了行10)のようにずれていると正しい合計値を求めることができません。
f:id:auroralights:20211231135543p:plain


SUMIFS関数の考え方

以下画像では、商品コードがA100かつ担当者コードが1101の数量の合計を『=SUMIFS($D$3:$D$10,$B$3:$B$10,J3,$G$3:$G$10,K3)』求めています。
f:id:auroralights:20211231001120p:plain

検索範囲『$B$3:$B$10』、検索条件『J3』、②検索範囲『$G$3:$G$10』、検索条件『K3』の両方に一致する行の③合計範囲『$D$3:$D$10』の数値データを合計値を返します。


SUMIFS関数の検索条件に使える『比較演算子』・『ワイルドカード』

SUMIF関数の引数の『検索条件』には、以下の比較演算子・ワイルドカードを使うことができます。比較演算子・ワイルドカードを使用する場合は検索条件とあわせて『"(ダブルクォーテーション)』で囲みます。

比較演算子 説明 使用例
= 等しい "=10"
<> 等しくない "<>男"
より大きい ">19"
< より小さい "<20"
>= 以上 ">=20"
<= 以下 ”<=19"

ワイルドカード 説明 検索条件の使用例
* 任意の文字列 "A*"
? 任意の1文字 "?田"


SUMIFS関数使用例

検索範囲・検索条件が1つだけの使用例

以下画像では商品コードがA100の受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,J3)』で求めています。
SUMIFとSUMIFSでの引数の指定順が異なることに注意です。
f:id:auroralights:20220101105530p:plain


検索範囲・検索条件を複数指定した使用例

以下画像では商品コードがA100かつ取引先がAの受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$G$3:$G$16,L3)』で求めています。
f:id:auroralights:20220101110649p:plain


以下画像では商品コードがA100かつ取引先がAかつ受注日が2022/1/1の受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$G$3:$G$16,L3,$I$3:$I$16,M3)』で求めています。
f:id:auroralights:20220101112038p:plain

ワイルドカード・比較演算子を指定した使用例

以下画像では商品コードがB10?かつ受注金額が1000以上の受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$F$3:$F$16,">=1000")』で求めています。
f:id:auroralights:20220101113129p:plain


以下画像では商品コードが*01かつ取引先がAかつ受注日が2022/1/2以降の受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$G$3:$G$16,L3,$I$3:$I$16,">=2022/1/2")』で求めています。
f:id:auroralights:20220101112502p:plain


検索条件で一意な行を指定して数値データを返す方法 (VLOOKUP的な使い方)

複数の検索範囲、検索条件で特定の1行を指定して、その行の合計範囲の数値データを求める使い方を紹介します。
検索範囲、検索条件で特定の1行を指定できれば、合計するものがないので、その行の合計範囲の数値データをそのまま返します。

以下画像では神戸支店の2022/1/1、2022/1/2、2022/1/3の売上をSUMIFS関数で求めています。
※売上日と支店名の組み合わせで一意になっています。
f:id:auroralights:20220101114703p:plain


神戸支店の2022/1/1の売上
=SUMIFS($D$3:$D$16,$B$3:$B$16,F3,$C$3:$C$16,G3)
神戸支店の2022/1/2の売上
=SUMIFS($D$3:$D$16,$B$3:$B$16,F4,$C$3:$C$16,G4)
神戸支店の2022/1/3の売上
=SUMIFS($D$3:$D$16,$B$3:$B$16,F5,$C$3:$C$16,G5)


以下画像では、左側の表を右側の表にSUMIFS関数で簡単に求めています。
f:id:auroralights:20220101115837p:plain
最初にセルG3に『=SUMIFS($D$3:$D$16,$B$3:$B$16,$F3,$C$3:$C$16,G$2)』を入力して、2022/1/1の大阪支店の売上を求めます。
次にその式をコピーして売上日ごとの各支店の売上を求めます。


SUMIFS関数、MAXIFS関数を使って最新日付の数値データを返す方法

以下画像では売上日、取引先、商品の組み合わせで一意なデータに対して、SUMIFS関数MAXIFS関数を使って、指定した取引先・商品の最新の売上を求めています。
f:id:auroralights:20220102125909p:plain

取引先が「A」かつ商品「みかん」の最新の売上は『=SUMIFS($E$3:$E$16,$B$3:$B$16,MAXIFS($B$3:$B$16,$C$3:$C$16,H3,$D$3:$D$16,I3),$C$3:$C$16,H3,$D$3:$D$16,I3)』で求めています。
ポイントは該当の取引先・商品の最新の売上日をMAXIFS関数で求めているところです。

同じように取引先「B」かつ商品「みかん」の最新売上は『=SUMIFS($E$3:$E$16,$B$3:$B$16,MAXIFS($B$3:$B$16,$C$3:$C$16,H4,$D$3:$D$16,I4),$C$3:$C$16,H4,$D$3:$D$16,I4)』で求めています。


複数の検索範囲・条件で一意な1行になるように指定して、数値データを返す使い方は頻繁に使うことはありませんが、覚えておくと非常に便利です。