フィルターをかけて画面の行を減らしても、SUMの合計は動かない。見えていない行も足しているからだ。見えている行だけを足すにはSUBTOTALかAGGREGATEを使う。
ただしこの2つも同じではない。200行の表で状態を4つ作って数えたら、行を手で隠したときだけ答えが分かれた。
以下の数は、Microsoft 365のExcel（バージョン16）で実際に計算したものだ。確認日は2026年9月22日。Windows 11で確かめている。表は200行で、金額の合計は2,010,000円、区分は東西南北の4つを順に振ってある。東は50行で495,000円になる。
SUMはフィルターをかけても動かない
4つの状態でそれぞれ数えた。
そのままのとき、SUMもSUBTOTALもAGGREGATEも2,010,000円で同じになる。
フィルターで東だけに絞ると、SUBTOTALとAGGREGATEは495,000円になった。見えている行数は50行だ。SUMだけ2,010,000円のまま動かない。
行を手で50行隠したときは分かれた。SUBTOTALの9は2,010,000円のままで、109は1,882,500円になる。差は127,500円だ。AGGREGATEの5は109と同じ1,882,500円になった。
フィルターと手で隠すのを両方やると、SUBTOTALの9も109も510,500円で同じになり、見えている行数は46行だった。
まとめると、SUBTOTALの9と109の差は、行を手で隠したときにしか出ない。フィルターで絞っただけなら、どちらも同じ答えになる。9と109で迷う場面は、実務ではそれほど多くない。
連番をSUBTOTALで振ると、最後の行がフィルターから外れる
フィルターをかけても飛ばない連番を作るのに、SUBTOTALの103を使う書き方がよく紹介されている。これを40行の表で試した。
連番を入れない表でフィルターをかけると、見えている行数は10行、合計は19,000円だった。これが正しい。
連番をSUBTOTALの103で振った表で同じフィルターをかけると、見えている行数が11行、合計が23,000円になった。4,000円多い。
原因は、範囲の最後の行にSUBTOTALが入っていることだ。Excelはその行を合計行とみなしてフィルターの対象から外す。だから条件に合わない最後の行が画面に残り、合計にも入る。
連番の式をAGGREGATEの3の5に替えると、10行と19,000円で合った。COUNTAで振ったときも合う。
=AGGREGATE(3,5,$C$11:$C11)範囲の始まりだけを固定して、終わりを固定しない。見えている行の数が上から順に返るので、フィルターをかけても1、2、3と続く。
同じ理由で、SUBTOTALを使った作業列を表の中に置くときも気をつける。最後の行に入っていれば、列がどこであっても同じことが起きる。在庫管理の表のように、明細の横に集計の列を足す形では出やすい。
表のすぐ下の合計行は、SUBTOTALでないと消える
同じ性質が、今度は役に立つ場面もある。
10行の表を作り、すぐ下の行に合計を置いてフィルターをかけた。SUBTOTALで書いた合計行は残った。AGGREGATEで書いた合計行と、SUMで書いた合計行は、どちらも隠れた。
合計行の区分の欄は空にしてある。フィルターの条件に合わないので、普通なら隠れる。SUBTOTALだけが合計行として扱われて残る。
だから置く場所で使い分けが決まる。表のすぐ下に合計行を置くならSUBTOTAL、データ行の中や表から離れた場所に書くならAGGREGATEになる。
配った表では、集計を表の上のほうに離して置いた。離しておけばどちらでも隠れないので、置き場所で悩まなくて済む。
小計を並べても、SUBTOTALは二重に足さない
区分ごとの小計を挟んだ表で、いちばん下に総合計を置くことがある。
3つの組に4行ずつ、合計12行で2,418円の表を作った。組ごとの小計をSUBTOTALで入れて、その全部を含む範囲に総合計を置く。
SUBTOTALで総合計を出すと2,418円になった。SUMで同じ範囲を足すと4,836円で、ちょうど2倍になる。小計の行をもう1回足しているからだ。
SUBTOTALは、範囲の中にある別のSUBTOTALを無視する。だから小計を挟んだ表でも、範囲の指定を細かく分けずに済む。SUMで総合計を出すなら、小計の行を外した範囲を指定するか、小計の行だけを足す。
エラーが1つあるとSUMもSUBTOTALも止まる
10個の数のうち1つを#N/Aにした列で数えた。
SUMは#N/Aを返す。SUBTOTALの9も#N/Aになる。AGGREGATEの9の6と、9の7は600と出た。エラーの行を飛ばして残りを足している。
VLOOKUPの結果をそのまま足している列では、これが効く。ただしエラーを飛ばすと、エラーがあること自体に気づかなくなる。飛ばすなら、エラーの数を別に数えておく。
=COUNTIF($A$2:$A$12,"#N/A")エラーの#N/Aが2件と、文字列として打った#N/Aが1件入った列で試すと、この式は2を返した。COUNTIFが数えるのはエラーのほうだけで、同じ見た目の文字列は数えない。この2つは別のものとして扱われる。
どのエラーでも数えたいならISERRORのほうが短い。こちらも同じ2を返した。
=SUMPRODUCT(--ISERROR($A$2:$A$12))SUMIFSはフィルターと関係なく足す
もう1つの取り違えが、SUMIFSとSUBTOTALの混同だ。
東で絞った画面でSUBTOTALは495,000円を出す。同じ画面でSUMIFSに東を指定しても495,000円になる。ここまでは合う。
フィルターを西に変えると、SUBTOTALは西の合計に変わるが、SUMIFSは東を指定したままなので495,000円のまま動かない。画面と数字が食い違う。
見えている行だけを条件付きで足したいときは、作業列に1か0を入れて、その列をSUBTOTALで足す。条件はSUMIFSではなくIFで書く。
作業列 =IF($C11=$H$1,$E11,0)
合計 =SUBTOTAL(9,$F$11:$F$210)作業列は条件に合う行だけ金額を返し、合わない行は0を返す。その列をSUBTOTALで足せば、フィルターで見えている行のうち条件にも合うものだけが残る。条件を2つ以上にするならIFを重ねるか、ANDでつなぐ。
作業列を使わずに1本の式で書く方法もあるが、SUMPRODUCTとSUBTOTALとOFFSETを組み合わせることになって、あとから読めなくなる。行が増えると計算も重い。作業列を1つ足すほうが直しやすい。
配った表では、見えている行の合計と、区分を指定した合計を並べて出して、その差も出している。差が0でなければ色が付く。重複の数え方のときと同じで、2つの数え方を並べておくと食い違いがその場で見える。
配ったブックの使い方
上の7行に集計を並べてある。フィルターを動かすと、SUM以外が動く。
黄色いセルに区分を1つ入れると、その区分の合計が出る。いちばん下の行が、見えている行の合計との差になる。フィルターの条件と入れた区分がそろっていれば0になり、ずれていれば色が付く。
連番はAGGREGATEで振ってある。行を足すときは最後の行の式を下にコピーする。
確かめていないこと
Excel 2007以前では試していない。AGGREGATEは2010からの関数なので、それより前の版では動かない。
Web版のExcelとGoogleスプレッドシートも確認していない。最後の行のSUBTOTALをフィルターから外す動きが同じかどうかは分からない。
テーブルとして書式設定した範囲では試していない。テーブルには集計行を出す機能があり、そこに入る式はSUBTOTALになる。同じ動きになるかは確認していない。
