仕組みと動作原理
SUMIFS関数はExcel業務を効率化する代表的な関数です。複数の条件をすべて満たすセル範囲の数値を合計します。 複雑な集計や参照処理を簡素化し、データ分析の精度と生産性を飛躍的に高めます。
💡 わかりやすい要約
Adds up all numbers in a range that meet one or more conditions (e.g., adding sales only for West region and Apple product).
"Think of this like a smart piggy bank. It only adds up coins that match all your criteria (like only summing quarters that are shiny)."
構文.
標準Excel表記法=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)引数一覧.
この関数で指定できるすべての引数について詳しく解説します。
The range of cells to sum.
指定例:C2:C6The first range evaluated with the criteria.
指定例:A2:A6The condition that must be met in criteria_range1.
指定例:"Fruit"The second range evaluated.
指定例:B2:B6The second condition that must be met.
指定例:">100"具体的な計算例
ステップ別の実務シナリオFilter & Sum Regional Sales
Sum the Total Sales of a specific Product in a specific Region.
| Region | Product Name | Sales Amount | Query Region | Result Total |
|---|---|---|---|---|
| West | Apple | 1200 | West | — |
| East | Orange | 950 | Apple | — |
| West | Orange | 800 | — | — |
=SUMIFS(C2:C6, A2:A6, "West", B2:B6, "Apple")Evaluates SUMIFS over the sample table cells, placing the verified output in cell E2.
数式ジェネレーター
ウィザードを開く引数を入力するだけで、シートにそのまま貼り付けて使える数式を作成できます。
=SUMIFS()出力される値の特性
Returns "Calculated output" based on sample evaluation criteria and active cell references.
実務上のベストプラクティス
- You can use logical operators (e.g. ">100", "<=50") and wildcards (e.g. "App*", "*east*") inside your criteria.
- Lock all range parameters with `$` so you can drag the formula down without shifting lookup ranges.
よくあるミスと注意点
- The sum_range and all criteria_range parameters MUST be identical in shape and dimensions, or SUMIFS will return a #VALUE! error.
各プラットフォーム対応表
実際のExcelおよびGoogleスプレッドシートの計算エンジン仕様に基づいた検証結果です。
Core standard function supported across all modern and legacy Microsoft Excel versions (365, 2021, 2019, 2016, 2013, 2010), as well as Excel for the Web and Mac.
100% compatible with Google Sheets. Uses identical argument order, data types, and standard calculation logic.
よくある質問と回答
技術解説What is the difference between SUMIF and SUMIFS?↓
SUMIF supports only one condition and puts the sum_range last: =SUMIF(range, criteria, [sum_range]). SUMIFS supports up to 127 criteria pairs and places the sum_range first: =SUMIFS(sum_range, criteria_range1, criteria1, ...).
Why does SUMIFS return 0 instead of the expected total?↓
SUMIFS requires ALL conditions to be met simultaneously (AND logic). If no single row satisfies all criteria, it returns 0. Also verify that numeric comparison operators are quoted (e.g., ">100" instead of >100).