Excelでフィルター後の合計を出す方法｜SUBTOTALの109と9の違い

Excelでフィルター後に表示されている行だけを合計するには、SUBTOTAL関数の集計方法に109を指定します。SUMと違い、フィルターで除外された行を合計に含めません。手動で非表示にした行も除外できます。

無料サンプルで操作と結果を確認

無料Excelサンプルをダウンロード（.xlsx）

登録不要／Excel 2016・2019・2021・2024・Microsoft 365に対応。説明の操作名はWindows版を基準にしています。

まず試す操作：B8の部署フィルターで「営業」だけを表示します。C4は6,000円、全件を足すC5は12,000円のままになれば成功です。

「まず試す」シートを開いてください。黄色は入力セルです。練習をやり直すときは、ダウンロードしたファイルのコピーを使います。

SUMではフィルターで隠れた行も合計する

サンプルには営業3件、経理2件の金額があります。まだ絞り込んでいない状態では、SUBTOTALもSUMも12,000円です。違いはフィルターをかけた後に現れます。

手順1：合計セルを表の外に置く

元データはA8:C13で、C9:C13が金額です。C4に表示中の合計を出す次の式を入力します。合計セルを金額の範囲に含めると循環参照になるので、表の上など、別の場所に置きます。

=SUBTOTAL(109,C9:C13)

109は「合計し、手動で非表示にした行も除外する」という集計方法です。サンプルのC5には比較用として次の式を入れています。

=SUM(C9:C13)

手順2：部署を営業に絞り込む

1. サンプルはフィルター付きのテーブルになっています。B8「部署」のフィルターボタンを開きます。
2. いったんすべての選択を外し、「営業」だけにチェックしてOKを押します。
3. 田中・鈴木・高橋の3行が表示されていることを確認します。
4. C4が6,000、C5が12,000であることを確認します。
5. 部署のフィルターを解除すると、C4も12,000へ戻ります。

自分の表にフィルターがない場合は、見出しを含むA8:C13を選び、「データ」→「フィルター」を設定します。合計セルC4・C5は選択範囲に含めません。フィルター操作と、別の場所に結果を出すFILTER関数は別の機能です。

結果の確かめ方：営業の3件を足す

営業は1,000円・3,000円・2,000円なので、合計6,000円です。サンプル右側のE8:F10には、部署条件で求めた照合用の合計を置いています。この画像は「フィルター操作後の画面」ではなく、期待する合計の確認表です。

9と109の違いは手動で隠した行の扱い

式	フィルターで非表示の行	手動で非表示の行
SUBTOTAL(9,範囲)	除外する	含める
SUBTOTAL(109,範囲)	除外する	除外する
SUM(範囲)	含める	含める

9でもフィルターで除外された行は集計されません。109を使うと、行番号のメニューで手動非表示にした行も除外できます。SUBTOTALは縦方向の一覧向けの関数で、列を隠しただけでは同じように集計対象から外れません。詳細はMicrosoftのSUBTOTALの仕様でも確認できます。

表示中の件数や平均も計算できる

目的	集計方法	例
合計	109	=SUBTOTAL(109,C9:C13)
数値の個数	102	=SUBTOTAL(102,C9:C13)
空でないセルの個数	103	=SUBTOTAL(103,A9:A13)
平均	101	=SUBTOTAL(101,C9:C13)

行数を数えたいなら、必ず値が入る担当名や管理IDの列を103で数えます。空欄がある列を使うと行数と一致しません。また、数式の空文字は完全な空セルとは異なり、COUNTA系の件数に含まれることがあります。営業の例では、担当名の件数は3、金額の平均は2,000円です。

合計が変わらない・合わないとき

症状	確認する場所
絞っても12,000のまま	合計セルがSUMではなくSUBTOTALになっているか。
一部の金額が足されない	金額が文字列になっていないか。
追加した行が集計されない	式の終了行が追加した行まで延びているか。
手動で隠した行が含まれる	集計方法が9ではなく109か。
更新が遅れる	計算方法が手動になっていないか。必要なら再計算する。

文字列の金額は数値への変換方法で整えます。固定範囲のC9:C13を使うサンプルでは、14行目以降を足したときに式の範囲も変更してください。テーブルの構造化参照を使えば、行の追加に合わせて範囲を追従させる設計もできます。

条件の行を別シートに取り出したい場合

今の表を絞って合計する用途にはSUBTOTALが向いています。元の表を残し、条件に合う行を別の場所へ表示したいなら、FILTERで条件に合う行を抽出する方法を使います。重複や空白を整理してから集計する場合は、空白行を除外する方法も役立ちます。

次に確認したいこと

• 合計に含まれない金額を直す：文字列の数値変換
• 条件に合う行を別の場所へ：FILTERの抽出
• 空の商品行を除く：FILTERで空白行を除外

自分の表に使う前に、サンプルで入力セルと結果の変化を確認しましょう。数式の参照範囲は、実際の表の列と最終行に合わせて変更してください。

無料Excelサンプルをダウンロード（.xlsx）

参考資料

• Microsoft：SUBTOTAL関数