SUMIFSで複数条件に基づいて合計する

SUMIFは、East地域のすべての売上のように、1つのルールに一致する値を合計することはすでにご存じでしょう。しかし、実際の疑問には通常、複数の条件があります。1月のEast地域の売上はいくらだったでしょうか。これは2つの条件を同時に指定する例です。

ここでSUMIFSが活躍します。末尾のSは、複数の条件を組み合わせられることを示しています。指定したすべてのテストに合格した行だけが合計に加算されます。

このレッスンでは、引数の順序を学び、最初の複数条件の合計を作成し、よくある間違いを避ける方法を学びます。

SUMIFSでは、SUMIFから想像する順序が逆になります。加算する数値が先に来て、その後に各条件をペアで指定します。

• sum_range — 合計する値
• criteria_range1、criteria1 — 最初のテスト
• criteria_range2、criteria2 — 2つ目のテスト

範囲と条件のペアは、最大127個の条件まで追加できます。次のパターンを声に出して読んでみましょう。「これを合計し、これがあれと等しく、さらにこれがあれと等しい場合」です。

列AにRegion、列BにMonth、列CにAmountが入った表を想像してください。East地域の1月の売上合計を求めるとします。

合計する金額は C:C にあります。最初の条件では A:A で「East」を確認し、2つ目の条件では B:B で「January」を確認します。両方の条件を満たす行だけがカウントされます。

これは、SUMIFSで最もよくあるエラーです。sum_rangeとすべてのcriteria_rangeは、同じ行数と列数の同じサイズでなければなりません。

sum_rangeが C2:C100 なのに、criteria_rangeの1つが A2:A99 だと、行が対応しないため数式は #VALUE! エラーを返します。

最も安全な方法は、すべての範囲で同じ開始行と終了行を使うか、A:A のような列全体を一貫して使うことです。

引用符で囲んだ「East」を直接指定しても機能しますが、柔軟なレポートではユーザーが選択できるようにします。地域をセル F1 に、月を F2 に入力し、それらのセルを条件として参照します。

F1またはF2を変更すると、合計がすぐに再計算されます。通常のセル参照には引用符が不要であることに注意してください。引用符は、数式内に文字列を直接入力する場合にのみ使います。

条件は完全一致のテキストに限りません。数値に対しては、比較演算子を引用符で囲んで使えます。

• ">100" — 100より大きい
• "<=50" — 50以下
• "<>0" — 0と等しくない

ここでは、East地域の金額を合計しますが、金額自体が100より大きい行だけを対象にします。sum_rangeとcriteria_rangeには同じ列を指定することもできます。

しきい値が入力されているのではなく、セルに入っている場合はどうすればよいでしょうか。">F1" と書くだけではいけません。これは文字列「F1」そのものを検索するためです。代わりに、演算子とセルを&記号で連結します。

つまり、">"&F1 はF1に入っている値より大きいという条件を作ります。この連結方法は、ユーザーが操作する動的なレポートに欠かせません。

SUMIFSは簡単に拡張できます。新しいルールごとに、範囲と条件のペアを追加します。たとえば、列DにSalespersonが入っているとします。East地域で1月にMariaが担当した売上を合計できます。

条件を追加するたびに、結果はさらに絞り込まれます。SUMIFSはAND論理を使うため、合計に含めるには1つの行が3つすべてのテストを満たす必要があります。

テキストの条件ではワイルドカードを使えます。アスタリスク * は任意の文字数に一致し、疑問符 ? はちょうど1文字に一致します。

• "North*" はNorth、Northeast、Northwestに一致します
• "*east*" はeastを含むあらゆる文字列に一致します

この方法では、「North」で始まる地域の金額を合計できます。地域名が同じ接頭辞を持つ場合に便利です。

引数の順序が逆なので、違いを覚えておく価値があります。

• SUMIF：range, criteria, [sum_range] — テストする範囲が先に来て、sum_rangeは省略可能な最後の引数です。
• SUMIFS：sum_range, criteria_range1, criteria1, ... — sum_rangeが常に先頭に来ます。

ヒント：条件が複数ある場合は、すぐにSUMIFSを使いましょう。条件が1つだけでも、SUMIFSを使ってSUM、COUNT、AVERAGEで同じパターンに統一する人は多くいます。

SUMIFSが0を返す場合、通常は数式が壊れているのではなく、すべての条件に一致する行がなかったことを意味します。テキスト内の見えない空白、つづりの不一致、数値がテキストとして保存されていないかを確認してください。

簡単な診断方法は、条件を1つずつ削除することです。条件を削除した時点で合計が表示されるなら、その条件がすべての行を除外していました。このように切り分けてテストする習慣を身につけると、複数条件の数式をすばやくデバッグできます。

SUMIFSの引数の順序についての理解度を確認しましょう。

複数の条件に同時に合致する数値を合計できるようになりました。

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_rangeが先頭に来ます。
• すべての範囲を同じサイズにしないと、#VALUE!になります。
• セルと比較するには ">"&F1 を使い、部分一致には * / ? を使います。
• 条件にはAND論理が使われます。行はすべての条件を満たす必要があります。

次は、COUNTIFSを使って複数の条件に合う行をカウントします。