本記事ではExcelのSUMIFS関数の使い方を説明します。
- SUMIFS関数とは。SUMIFS関数の書式
- SUMIFS関数の検索条件に使える『比較演算子』・『ワイルドカード』
- SUMIFS関数使用例
- 検索条件で一意な行を指定して数値データを返す方法 (VLOOKUP的な使い方)
- SUMIFS関数、MAXIFS関数を使って最新日付の数値データを返す方法
SUMIFS関数とは。SUMIFS関数の書式
SUMIFS関数は、1つもしくは複数の検索条件に一致した『行』の数値データを合計する関数です。
もう少し詳細に書くと、指定した検索範囲の中で検索条件に一致する行を検索し、その行の合計範囲の数値データを合計します。
複数の検索範囲・検索条件を指定する場合は、複数の検索範囲・検索条件に一致する行を検索し、その行の合計範囲の数値データを合計します。
検索条件には比較演算子、ワイルドカードを使うことができます。
SUMIFS関数の書式は以下の通りです。
SUMIFS関数の書式
=SUMIFS(合計範囲, 検索範囲1, 検索条件1[, 検索範囲2, 検索条件2,・・・])
引数の合計範囲、検索範囲1、検索条件1は必須です。
SUMIF関数の書式は「=SUMIF(検索範囲, 検索条件, 合計範囲)」とSUMIFS関数と指定する順番が異なることに注意しましょう。
書式に迷うことがあれば、Googleで検索したり、数式バーに=SUMIF(、=SUMIFS(など関数を入力したりして確認しましょう。
※検索範囲、合計範囲の注意点
検索範囲、合計範囲の開始行、終了行を必ず合わせます。例えば検索範囲が「A1:A10」の場合は合計範囲を「C1:C10」とします。(検索範囲、合計範囲ともに開始が1行目、終了が10行目)
もし、検索範囲「A1:A10」(開始行1:終了行10)、合計範囲「C5:C10」(開始行5:終了行10)のようにずれていると正しい合計値を求めることができません。
SUMIFS関数の考え方
以下画像では、商品コードがA100かつ担当者コードが1101の数量の合計を『=SUMIFS($D$3:$D$10,$B$3:$B$10,J3,$G$3:$G$10,K3)』求めています。
①検索範囲『$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での引数の指定順が異なることに注意です。
検索範囲・検索条件を複数指定した使用例
以下画像では商品コードがA100かつ取引先がAの受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$G$3:$G$16,L3)』で求めています。
以下画像では商品コードが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)』で求めています。
ワイルドカード・比較演算子を指定した使用例
以下画像では商品コードがB10?かつ受注金額が1000以上の受注金額の合計を『=SUMIFS($F$3:$F$16,$B$3:$B$16,K3,$F$3:$F$16,">=1000")』で求めています。
以下画像では商品コードが*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")』で求めています。
検索条件で一意な行を指定して数値データを返す方法 (VLOOKUP的な使い方)
複数の検索範囲、検索条件で特定の1行を指定して、その行の合計範囲の数値データを求める使い方を紹介します。
検索範囲、検索条件で特定の1行を指定できれば、合計するものがないので、その行の合計範囲の数値データをそのまま返します。
以下画像では神戸支店の2022/1/1、2022/1/2、2022/1/3の売上をSUMIFS関数で求めています。
※売上日と支店名の組み合わせで一意になっています。
神戸支店の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関数で簡単に求めています。
最初にセルG3に『=SUMIFS($D$3:$D$16,$B$3:$B$16,$F3,$C$3:$C$16,G$2)』を入力して、2022/1/1の大阪支店の売上を求めます。
次にその式をコピーして売上日ごとの各支店の売上を求めます。
SUMIFS関数、MAXIFS関数を使って最新日付の数値データを返す方法
以下画像では売上日、取引先、商品の組み合わせで一意なデータに対して、SUMIFS関数とMAXIFS関数を使って、指定した取引先・商品の最新の売上を求めています。
取引先が「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行になるように指定して、数値データを返す使い方は頻繁に使うことはありませんが、覚えておくと非常に便利です。
