SUMIFで条件に基づいて合計する

通常のSUMは、範囲内のすべての数値を合計します。しかし、実際には一部だけを合計したいことがよくあります。たとえば、A列に地域、B列に金額が入った売上シートを考えてみましょう。すべての売上の合計ではなく、East地域だけの合計が必要です。

これは条件付き合計で、そのために用意されている関数がSUMIFです。一致する条件が満たされた場合だけ数値を加算し、それ以外は自動的に除外します。

SUMIFは、順番に3つの要素を取ります。

• range条件と照合するセル範囲
• criteria検索する条件
• sum_range実際に合計するセル範囲

つまり、スプレッドシートはrangeを調べてcriteriaを満たす行を見つけ、その行に対応するsum_rangeのセルを合計します。

A2:A10に地域名、B2:B10に売上金額が入っているとします。Eastの売上だけを合計するには、A列でEastという単語を検索し、一致する金額をB列から加算します。

スプレッドシートは各地域セルを確認します。Eastと表示されている行では、その行の金額を取得して合計に加えます。それ以外の地域は無視されます。

条件が単語の場合は、"East"のように二重引用符で囲みます。引用符がないと、スプレッドシートはEastを名前やラベルだと解釈し、0を返す可能性があります。

照合では大文字と小文字が区別されないため、"east"と"East"は同じ行に一致します。ただし、余分なスペースがないことも含め、スペルがデータと完全に一致していることを確認してください。

条件を手入力しても問題ありませんが、セルに入力しておくとより扱いやすくなります。D1にEastという単語が入っている場合は、テキストを直接書く代わりにD1を参照します。

これでD1の地域を変更するだけで、合計がすぐに更新されます。数式を再利用でき、レポートの簡単な操作スイッチとして1つのセルを使えるようになります。

条件はテキストである必要はありません。ちょうど500に等しいすべての金額を合計するには、条件として数値をそのまま使用します。

テストする列と合計する列が同じ場合は、3番目の引数を省略することもできます。ここではB列をテストと合計の両方に使うため、Bが500の行だけが加算されます。

SUMIFは完全一致以外にも対応できます。引用符内に比較演算子を入れると、数値の範囲を合計できます。

• ">100" 100より大きい
• "<=50" 50以下
• "<>0" 0以外

この数式は、100を超えるすべての売上を合計します。演算子と数値は、どちらも1組の引用符の中に入れます。

しきい値がセルに入っている場合はどうでしょうか。たとえば、D1に100が入っていて、それより大きい金額を求めたいとします。">D1"とは書けません。これは文字列D1そのものとして扱われるためです。代わりに、アンパサンドで演算子とセルを結合します。

アンパサンドは">"というテキストとD1の値をつなぎ、条件">100"をその場で作成します。

rangeとsum_rangeは、高さを同じにし、同じ行から始める必要があります。地域名がA2:A10にある場合、金額はB3:B11ではなくB2:B10に入っていなければなりません。

対応がずれていると、スプレッドシートは間違った地域と金額を組み合わせ、気付かないまま誤った合計を返します。必ず両方の範囲がまったく同じ行を対象としていることを再確認してください。

A列にカテゴリ、B列に費用が入った経費シートを想像してください。Travel、Food、Officeの合計を表示する小さな集計表を作りたいとします。

D2、D3、D4の各セルにカテゴリ名を入力し、そのラベルセルを参照するSUMIFを1つ作成します。それを下方向にコピーすると、各行で対応するカテゴリの合計が計算されます。1つの数式パターンから、3つの小計をすぐに作成できます。

前の例では、列全体を表すA:AとB:Bを使用していました。行が追加され続ける場合でも範囲を拡張する必要がないため便利です。

ただし、見出し行のテキストが誤って条件に一致しないことと、両方の列が対応していることを確認してください。非常に大きなシートでは、A2:A1000のように範囲を絞ると計算が少し速くなる場合があります。

これで、SUMIFを使って条件付きで数値を合計する方法が分かりました。重要なポイントは次のとおりです。

• 順番はrange、criteria、sum_rangeです。
• テキスト条件には引用符が必要で、照合では大文字と小文字が区別されません。
• ">100"のような演算子は引用符の中に入れます。演算子とセルを結合するには&を使います。
• rangeとsum_rangeは同じ行に対応させます。

次は、COUNTIFを使って条件に一致する行を数えます。