スプレッドシートのSUM関数の使い方｜合計が合わない・0になる時の確認順

スポンサーリンク

売上表や経費精算の最後の行に、合計を出したい。そんなとき一番の近道が、Googleスプレッドシートの SUM 関数です。

ところが「電卓で足した数字と合わない」「なぜか合計が0になる」とつまずく人も少なくありません。原因の多くは、数字に見えて実は文字列のセルや、範囲指定のずれ。見た目では気づきにくいのが厄介なところですよね。

この記事では、スプレッドシートの SUM 関数の構文と最短の使い方から、範囲指定の書き分けまでを順に解説します。後半では、合計が合わない時の確認順と、SUMIF・SUBTOTAL などとの使い分けも表で整理しました。

スプレッドシートのSUM関数の構文と最短の使い方

SUM関数（読み方: サム）は、指定した数値やセル範囲を合計する関数です。英語の「sum（合計）」がそのまま名前になっています。

まずは構文と、コピペで動く例から見ていきましょう。

基本構文とコピペで動く例

=SUM(値1, [値2, ...])
引数必須/省略内容
値1必須合計する最初の数値、またはセル範囲
値2, …省略可追加で合計する数値、またはセル範囲

引数はカンマで区切って並べます。Googleスプレッドシートでは、引数の数は任意に指定できる仕様です。実務では範囲をまとめて指定するので、数が問題になる場面はまずないでしょう。

例として、次の売上表で合計を出してみます。

 AB
1月売上（円）
24月120,000
35月98,000
46月135,000
57月110,000
68月87,000

B7 に次の数式を入れると、4〜8月の合計「550,000」が表示されます。

=SUM(B2:B6)

「B2:B6」のコロンは「B2からB6まで」という意味です。数式の入力中にセルをドラッグすれば、範囲は自動で入ります。

セルや数値を直接並べる書き方もできます。

=SUM(B2, B4, B6)
=SUM(B2:B6, 50000)

1つ目は4月・6月・8月だけを足すので、結果は342,000。2つ目は5か月分に50,000を加えて600,000になります。

離れたセルを選ぶときは、数式の入力中に Ctrl（Mac は command）を押しながらクリックすると便利です。クリックしたセルが範囲に加わっていきます。

関数を使わず合計を見る（画面右下の合計表示・Σ）

「数式を残すほどではないけど、合計だけ知りたい」という場面もありますよね。パソコン版では、数値の入ったセルを選択するだけで画面右下に「合計: 550,000」のように表示されます。

右下の［合計］をクリックすると、平均・最小・最大・カウント・個数にも切り替えられます。ただし公式ヘルプには、一部の形式の数値や通貨では動作しないとの注記があります。表示されないときは、数式で確かめるのが確実です。

数式として残したい場合は、ツールバーの「関数」ボタン（Σ）から SUM を選ぶ方法もあります。入力された範囲が意図どおりかは、確定する前に数式バーで確認しておきましょう。

SUM関数の範囲指定の書き分け（連続・複数・列全体・別シート）

SUM の結果が正しいかどうかは、範囲の書き方で大きく左右されます。データが増え続ける表なのか、別のシートにあるのかで、向く書き方が変わるためです。

書き分け比較表

書き方例データ追加への強さ注意点向く場面
連続範囲=SUM(B2:B6)弱い表の末尾に足した行は範囲外行数が決まった表
複数範囲=SUM(B2:B6, D2:D6)弱い範囲の指定漏れに注意離れた列をまとめる
列全体=SUM(B:B)強い見出しやメモの数値も足される毎日行が増える台帳
途中から列末まで=SUM(B2:B)強い表の下に別の数値を置かない見出し行を除きたい台帳
別シート=SUM('4月売上'!B2:B50)範囲しだいシート名を引用符で囲む月別シートの集計
他ファイル=SUM(IMPORTRANGE(…))範囲しだい初回にアクセス許可が必要別ファイルの数値を集計

連続範囲は、範囲の途中に行を挿入すれば自動で広がります。困るのは、表の一番下に新しい行を書き足したとき。その行は範囲の外になり、合計に入りません。

列全体（B:B）にすると、行を足しても数式を直す必要はありません。その代わり、列の中にある数値はすべて合計の対象になります。合計セルを同じ列に置くと自分自身を含む循環参照になるので、別の列に置きましょう。

別シート・他ファイル（IMPORTRANGE）の合計

同じファイル内の別シートは、「シート名!範囲」の形で指定します。シート名に空白や日本語・記号が含まれるときは、シングルクォーテーションで囲むのがルールです。囲んでも害はないので、迷ったら囲んでおくと安心でしょう。

=SUM('4月売上'!B2:B50, '5月売上'!B2:B50)

別のスプレッドシートファイルの数値は、IMPORTRANGE 関数で読み込んでから SUM で合計します。

=SUM(IMPORTRANGE("スプレッドシートのURL", "'4月売上'!B2:B50"))

初回は #REF! が表示され、「アクセスを許可」をクリックするまで値が入りません。許可すると、このファイルの編集者は全員、元ファイルのどの範囲でも読めるようになります。共有の範囲が広がる点は、あらかじめ意識しておきたいところです。

ARRAYFORMULA・小計の足し込みなどの組み合わせ例

SUM は他の関数と組み合わせると、守備範囲がぐっと広がります。実務でよく使う3つを紹介しましょう。

1つ目は、単価×数量を行ごとに計算してから合計する形です。ARRAYFORMULA は、範囲全体にまとめて計算を適用する関数です。

 ABC
1商品単価数量
2ノート50010
3ファイル1,2003
4ペン8005
=SUM(ARRAYFORMULA(B2:B4*C2:C4))

結果は 5,000＋3,600＋4,000 で 12,600。作業列を作らずに済むのが利点です。詳しくはスプレッドシートのARRAYFORMULA関数の使い方で解説しています。

2つ目は、文字列になった数値を変換しながら合計する形です。

=SUM(ARRAYFORMULA(VALUE(B2:B6)))

VALUE は、文字列の数値を本物の数値に変換する関数。範囲内に「未定」などの変換できない文字があると、エラーになる場合があります。

3つ目は、小計行がある表の総計です。B5 と B9 が小計のセルなら、小計だけを足せば二重カウントを防げます。

=SUM(B5, B9)

小計そのものを SUBTOTAL(9, …) で出しておく方法もあります。SUBTOTAL は、範囲内にある SUBTOTAL のセルを無視する仕組み。そのため総計を =SUBTOTAL(9, B2:B9) と全体で取っても、小計は重複しません。

SUM関数で合計が合わない・0になる時の確認順

ここが一番知りたかった、という方も多いはず。上から順に確認していくと、原因を早く絞り込めます。

症状→原因→直し方の表

確認順症状原因直し方
1一部の数字が左寄せ数値が文字列として入っているVALUE で変換、または入れ直す
2同じ数字に見えるのに足されない前後の空白・全角数字VALUE(TRIM())・VALUE(ASC())
3列ごと足されず0になる書式が「書式なしテキスト」書式を「自動」にして入れ直す
4最近足した行が入らない範囲が古いまま（範囲ずれ）範囲を広げる・B2:B 形式にする
5合計が大きすぎる見出しの数値や小計が混入範囲を見直す・SUBTOTAL を使う
6画面の数字と合わない非表示行・フィルタで隠れた行SUBTOTAL(109, …) を使う

最初に見るのは、セルの配置です。標準の配置では数値は右寄せ、文字列は左寄せで表示されます。一部だけ左に寄っている数字があれば、それが文字列になっている可能性が高いでしょう。

文字列の数値は、範囲で指定した SUM ではエラーにならず、合計から外れるのが一般的な挙動です。たとえば先ほどの表で B4 の「135,000」が文字列なら、合計は 415,000 と少なく表示されます。列の数値がすべて文字列だと、合計が0になることもあります。

数え漏れを見つけるには、=COUNT(B2:B6) が手軽です。COUNT は数値のセルだけを数えるので、5行あるのに「4」と出れば1つが数値ではないとわかるはず。気になるセルは =ISNUMBER(B4) で調べ、FALSE なら文字列と判断できます。

COUNT の使い方はスプレッドシートのCOUNT関数の使い方にまとめています。

文字列の数値を数値に直す（VALUE・ARRAYFORMULA・書式）

文字列になった数値は、状態に合わせて次の式で直します。

=VALUE(B4)
=VALUE(TRIM(B4))
=VALUE(ASC(B4))

1つ目が基本形です。2つ目は、コピー元から付いてきた前後の空白を TRIM で取り除いてから変換します。

3つ目は全角数字への対処です。スプレッドシートの VALUE は「１３５０００」のような全角数字を変換できず、#VALUE! を返します。ASC で半角にしてから VALUE に渡すのがコツ。

Excel の感覚で VALUE だけ書くと、ここでつまずきます。

列ごと直したいときは、空いている列に =ARRAYFORMULA(VALUE(B2:B6)) を入れて変換します。結果をコピーし、元の列に「値のみ貼り付け」で戻せば完了です。

書式が「書式なしテキスト」の列は、表示形式を「数字」→「自動」に変えておきましょう。ただ、書式を変えただけでは入力済みの値が文字列のまま残ることがあります。その場合は値を入れ直すか、上の式で変換してください。

VALUE の細かい挙動はスプレッドシートのVALUE関数の使い方で詳しく扱っています。

#VALUE!・#REF!・#NAME? を短く確認

合計が数字ではなくエラーになる場合は、表示されたエラーの種類で原因を絞れます。

エラーよくある原因直し方
#NAME?関数名のつづり間違い（=SUN(B2:B6) など）正しく SUM と書き直す
#REF!参照していた行・列・シートを削除した数式の範囲を指定し直す
#REF!IMPORTRANGE のアクセスを未許可「アクセスを許可」をクリック
#VALUE! など範囲内にエラーのセルがあるエラーの出ているセルを先に直す

範囲の中に #N/A や #VALUE! のセルが1つでもあると、SUM の結果も同じエラーになることがあります。SUM の式ではなく、元のセルを先に確認しましょう。

なお、SUMIF や COUNTIF のような条件付き集計で0になる場合は、原因が少し異なります。SUMIF・COUNTIFが0になる原因もあわせて確認してみてください。

SUM・SUMIF・SUMIFS・SUBTOTAL・SUMPRODUCTの使い分け

スプレッドシートで合計を出す関数は、SUM のほかにもいくつかあります。「全部足す」なら SUM で十分ですが、条件や非表示行が絡むと別の関数が向いています。

用途別の使い分け表

関数向いている用途書き方の例詳しい解説
SUM条件なしで全部足す=SUM(C2:C100)この記事
SUMIF1つの条件に合う行だけ足す=SUMIF(B:B, "本店", C:C)SUMIF関数
SUMIFS複数の条件に合う行だけ足す=SUMIFS(C:C, B:B, "本店", A:A, "4月")SUMIFS関数
SUBTOTAL非表示行の除外・小計の自動除外=SUBTOTAL(9, C2:C100)SUBTOTAL関数
SUMPRODUCT単価×数量などの積を合計=SUMPRODUCT(B2:B4, C2:C4)SUMPRODUCT関数

先ほどの単価×数量の表なら、=SUMPRODUCT(B2:B4, C2:C4) でも 12,600 になります。ARRAYFORMULA を使う書き方より短く書けるのが SUMPRODUCT の良さです。

支店別・月別の合計を一覧表にまとめたいなら、スプレッドシートのQUERY関数の使い方も検討してみてください。

非表示行・フィルタ行の扱いの違い

「画面に見えている行だけ合計したい」ときに迷いやすいのが、SUM と SUBTOTAL の違いです。

関数手動で非表示にした行フィルタで隠れた行
SUM含める含める
SUBTOTAL(9, …)含める含めない
SUBTOTAL(109, …)含めない含めない

SUM は、隠れている行もすべて足します。SUBTOTAL の「9」はフィルタで隠れた行だけを除き、「109」は手動で非表示にした行も除く仕組みです。

スプレッドシートのSUM関数でよくある質問

列全体指定で合計がおかしくなるのはなぜ？

=SUM(B:B) は、B列にある数値をすべて足すためです。見出しに「2026」のような数値が入っていたり、表の下にメモの数値があったりすると、それも合計に入ります。

見出しを除くなら =SUM(B2:B) と書き、表の下には別の数値を置かないようにしましょう。

フィルタで隠した行を除いて合計するには？

SUM ではなく =SUBTOTAL(9, B2:B100) を使います。フィルタで隠れた行は、9 でも 109 でも合計から外れます。

手動で非表示にした行も除きたいときは、109 を選んでください。

文字が混ざっていてもSUMはエラーにならない？

範囲の中にある「未定」「-」などの文字は、通常は計算から外れるだけでエラーにはなりません。ただ、数字の文字列も同じように外れるため、合計が少ないことに気づきにくいのが落とし穴です。

一方、範囲内に #N/A などのエラー値があると、結果もエラーになることがあります。合計が怪しいときは、COUNT で数値のセルの数を確かめると安心です。

まとめ

スプレッドシートの SUM 関数は、=SUM(範囲) だけで使える一番身近な集計関数です。ただし「合計が合わない」ときの原因は、関数ではなくデータ側にあることが多いもの。最後にポイントを振り返っておきましょう。

  • 構文は =SUM(値1, [値2, ...])。範囲・セル・数値を混ぜて指定できる
  • 合計だけ見たいなら、セルを選んで画面右下の合計表示を見る
  • 行が増える表は B2:B 形式、別シートは 'シート名'!範囲、他ファイルは IMPORTRANGE
  • 合わないときは、左寄せ→空白・全角→書式→範囲ずれ→混入→非表示行の順に確認
  • 文字列の数値は VALUE、全角は VALUE(ASC()) で直す
  • 条件付きは SUMIF・SUMIFS、隠れた行の除外は SUBTOTAL、積の合計は SUMPRODUCT

合計の次に平均を出したいときは、スプレッドシートのAVERAGE関数の使い方も参考にしてみてください。

スポンサーリンク
タイトルとURLをコピーしました