SUMIFSで支店別の売上を合計したら、0になる。COUNTIFSで数えた件数が、元の表と合わない。日付や「以上・以下」の条件を入れたとたん、結果がおかしくなる。
最初に確認したいのは、エラーか0か、数字がずれているのかです。
この記事でわかること
- SUMIFS・COUNTIFSで合計・件数が合わない8つの原因
- 起きている問題から原因をしぼる確認の順番
- ずれた集計のまま報告しないための検算のしかた
結論：「範囲」「条件」「データ」の3か所に分けて探す
SUMIFS・COUNTIFSの合計や件数が合わないときは、範囲・条件・データの3か所を順に見ると、原因をしぼれます。
SUMIFSは、条件に合う行だけを合計する関数です。COUNTIFSは、条件に合う行の数を数えます。
この記事では、架空の売上表を例にします。A列が日付、B列が支店名、C列が売上です。データは2行目から101行目まであります。
=SUMIFS(C2:C101,B2:B101,"東京")
この数式は「B列が東京の行だけ、C列の売上を合計する」という意味です。C2:C101を合計範囲(合計する列)、B2:B101を条件範囲(条件を当てる列)と呼びます。
見る場所ごとの原因は次のとおりです。
- 範囲：範囲の大きさ・位置・引数(関数の( )に入れる値)の順番
- 条件：比較の書き方・同じ列の2つの値・ワイルドカード(「*」などの記号)
- データ：文字列の数値や日付・空白と表記ゆれ
関数の基本から確認したい場合は、次の記事が入口になります。
まずここから確認：起きている問題から、8つの原因をしぼる
エラー・0・少しずれる・多すぎるのどれが起きているかで、最初に疑う原因が決まります。
画面の名前はWindows版のMicrosoft 365を前提にしています。Mac版などでは違うことがあります。
| 起きている問題 | 疑う原因 | 最初に見る場所 |
|---|---|---|
| #VALUE!になる | 1. 範囲の大きさが違う | 各範囲の最後の行番号 |
| エラーはないが少しずれる | 2. 範囲の位置のずれ | 各範囲の最初の行番号 |
| SUMIFから書き換えたら0 | 3. 引数の順番 | 数式の1番目の範囲 |
| 以上・以下・日付で0か少ない | 4. 比較の書き方 | 条件の「"」と「&」 |
| 2つの値を条件にしたら0 | 5. 同じ列の2つの値 | 同じ条件範囲が2回あるか |
| 件数が多すぎる | 6. ワイルドカード | 条件の「*」「?」 |
| 一部の行だけ足されない | 7. 文字列の数値・日付 | セルの左揃え・緑の三角 |
| 同じに見える値が数えられない | 8. 空白と表記ゆれ | 条件範囲のセルの文字数 |
この表の1〜3は範囲、4〜6は条件、7と8はデータの原因です。
どの問題か分からないときは、表の上から順に見ます。1〜3は、数式バー(シートの上にある、セルの中身を表示する欄)だけで確かめられます。
原因と直し方：範囲の3つ、条件の3つ、データの2つ
8つの原因は、範囲・条件・データのどこを直すかが決まっています。
原因1：合計範囲と条件範囲の大きさが違う
問題と原因：SUMIFSでは、合計範囲と条件範囲を同じ行数・列数にする必要があります。大きさが違うと、#VALUE!エラーになります。
「=SUMIFS(C2:C101,B2:B120,"東京")」は、C列が100行、B列が119行なのでエラーです。120行目まで行を足し、B列の範囲だけ広げると起きます。
COUNTIFSも、2つ目以降の条件範囲は、1つ目と同じ行数・列数にする必要があります。
SUMIFは、範囲の大きさが違ってもエラーになりません。SUMIFからSUMIFSに書き換えたときに、初めてエラーが出ることがあります。
確認方法：数式バーで、すべての範囲の最初と最後の行番号を見ます。
どう直すか：すべての範囲を同じ行にそろえます。この例なら、どちらも2行目から120行目にします。
原因2：範囲の大きさは同じでも、位置がずれている
問題と原因：SUMIFSは、各範囲の同じ位置にあるセルを組にして判定します。範囲の大きさが同じなら、開始行がずれていてもエラーになりません。
たとえば「=SUMIFS(C2:C101,B3:B102,"東京")」では、C2の売上とB3の支店名が組になります。1行ずれた組で判定されるため、合計が少しずれます。
数式を下や横にコピーしたときも、範囲が一緒にずれます。
確認方法：数式バーで、各範囲の最初の行番号がそろっているかを見ます。コピーした先のセルでも同じように見ます。
どう直すか：開始行と終了行を、すべての範囲で同じにします。コピーして使う数式は、数式バーで範囲を選んでF4キーを押します。「$C$2:$C$101」のような絶対参照(コピーしても動かない参照)になり、ずれなくなります。
原因3：引数の順番がSUMIFと逆になっている
問題と原因：SUMIFSでは合計範囲が1番目の引数ですが、SUMIFでは3番目です。SUMIFの順番のまま書くと、合計する列と条件を当てる列が入れ替わります。
同じ合計を、SUMIFとSUMIFSで書くと次のとおりです。
- SUMIF：
=SUMIF(B2:B101,"東京",C2:C101) - SUMIFS：
=SUMIFS(C2:C101,B2:B101,"東京")
「=SUMIFS(B2:B101,C2:C101,"東京")」と書くと、売上のC列から「東京」を探すことになります。当てはまる行がなければ、結果は0です。
確認方法：数式の1番目の範囲が、合計したい列かを見ます。
どう直すか：1番目に合計範囲を書き、そのあとに条件範囲と条件の組を書きます。
原因4：「以上・以下」や日付の条件の書き方が違う
問題と原因：比較演算子(>=、<=などの記号)を使う条件は、「">=100"」のように「"」で囲みます。セルの値と比べるときは、記号とセルを「&」でつなぎます。
「">="&E1」は「E1の値以上」という意味になります。「">=E1"」と書くと、セルの値ではなく「E1」という文字と比べるため、0になることがあります。
E1に開始日、E2に終了日を入れて期間をしぼる数式は、次のとおりです。
=SUMIFS(C2:C101,A2:A101,">="&E1,A2:A101,"<="&E2)
A列の日付に時刻も入っていると、終了日の行が外れることがあります。E2が10/31なら、「10/31 15:00」は10/31より後の値になるためです。
COUNTIFSでは、条件にした空のセルは0として扱われます。E1を入れ忘れると、件数が0や思わぬ数になることがあります。
確認方法：条件の「"」と「&」の位置を見ます。条件にしたセルに、値が入っているかも見ます。
どう直すか：記号は「"」で囲み、セルとは「&」でつなぎます。時刻が入る日付は、終了日を「"<"&E2+1」(終了日の翌日より前)にします。
原因5：同じ列に、両立しない2つの値を条件にしている
問題と原因：SUMIFS・COUNTIFSは、すべての条件を満たす行だけを対象にします(AND条件)。「東京または大阪」のようなOR条件は、1つのSUMIFSでは書けません。
同じB列に「"東京"」と「"大阪"」の2つの条件を並べると、両方を満たす行がないため結果は0です。
=SUMIFS(C2:C101,B2:B101,"東京",B2:B101,"大阪")
確認方法：同じ条件範囲が2回出てくるかを見ます。2回出てくるなら、その2つの条件を同時に満たす行があるかを考えます。
同じA列に「以上」と「以下」を並べるのは、両方を満たす行があるので問題ありません。
どう直すか：条件を1つずつにしたSUMIFSを2つ作り、「+」で足します。「"東京"」のSUMIFSと「"大阪"」のSUMIFSを足す形です。
原因6：条件に「*」や「?」が入っている
問題と原因：条件の「*」は任意の文字列、「?」は任意の1文字として扱われます。この記号をワイルドカードと呼びます。
「"東京*"」は「東京」だけでなく、「東京駅前」「東京第2」にも当てはまります。意図より多く合計したり、数えたりすることがあります。
条件にした商品コードに「*」「?」そのものが入っていると、別のコードまで当てはまることがあります。
確認方法：条件に「*」「?」が入っていないかを見ます。条件にしたセルの値も見ます。
どう直すか：記号を文字として探すときは、前に「~」(チルダ)を付けます。たとえば「"A~*B"」です。部分一致が要らないなら、「*」を外して値をそのまま書きます。
原因7：合計範囲の数値や、日付が文字列で入っている
問題と原因：合計範囲のセルに、数値が文字列として入っていることがあります(文字として入った数字)。その行は、条件に合っていても合計に入らないことがあります。
日付が文字列で入っていると、「以上・以下」の条件に当てはまらないことがあります。別のシステムから取り込んだデータや、貼り付けたデータで起きることがあります。
確認方法：文字列の数値は、セルの左上に緑の三角(エラー インジケーター)が出ることがあります。文字列の日付は、セルの中で左揃えになります(揃えを変えていない場合)。
どう直すか：数値は、セルを選ぶと横に出る「!」のボタンを押し、「数値に変換」を選びます。日付は、空いている列に「=DATEVALUE(A2)」と入れて日付の値に直し、A列に値として貼り付けてから日付の表示形式にします。
文字列の数値を列ごとに見つける方法(COUNT関数とCOUNTA関数の比べ方)は、次の記事にまとめています。
原因8：余分な空白や、表記の違いがある
問題と原因：ワイルドカードを使わない条件は、セルの値と文字がそろっているときだけ当てはまります。「東京 」のように末尾に空白があると、「"東京"」の条件に当てはまりません。
「東京」と「東京都」、「(株)」と「株式会社」のような表記の違いも、別の値として扱われます。一方、英字の大文字と小文字は区別されません。
確認方法：空いているセルに「=B2="東京"」と入れ、FALSEになるかを見ます。FALSEなら「=LEN(B2)」で文字数を数えます。「東京」なら2のはずです。
どう直すか：TRIM関数で余分な空白を取り除きます。表記の違いは、置換などで1つの書き方にそろえます。
現場目線の注意点：合わない差を、手で足して合わせない
SUMIFSの合計が合わないときは、差を手で足さず、どの行が条件から漏れたかを探します。
差の金額を、別のセルに手で入れて合わせない。原因が残るため、次に集計したときも同じ行が漏れます。数式の結果と手の調整が混ざり、後から見た人には区別がつきません。
分類ごとの合計を足して、全体の合計と比べる。支店ごとのSUMIFSを足した値と、C列全体のSUMを比べます。差があれば、どの支店にも当てはまらなかった行か、2回数えた行があります。
COUNTIFSなら、支店ごとの件数の合計を、B列のCOUNTA関数(空白以外のセルを数える関数)の結果と比べます。
それでも合わないとき：数式の途中、別のブック、数式のセルの位置を見る
8つを確かめても合わないなら、数式の途中・閉じたブック・数式のセルの位置を確かめます。
数式の途中は、数式のセルを選び、「数式」タブの「数式の検証」で、計算を1段ずつ見られます。条件を1つずつ外した数式と比べるのも、どの条件で外れるかを探す手がかりになります。
別のブックの範囲を参照していて、そのブックが閉じていると、#VALUE!エラーになります。参照先のブックを開き、F9キーで再計算します。
別のシートの範囲は、ブックが同じならこの問題は起きません。
合計範囲をC列全体(C:C)にして、同じC列に数式を入れると、循環参照になります。循環参照は、数式が自分のセルを参照している状態です。数式は、合計範囲の外の列に置きます。
業務への着地：エラーにならないずれを、報告の前に見つける
SUMIFSのずれの多くはエラーにならないため、確認の順番と検算を決めておくと、報告の前に気づけます。
8つの原因のうち、#VALUE!エラーになるのは主に原因1です。ほかは、それらしい数字が出るため、そのまま報告書(会議や上司に出す集計表)に写してしまうことがあります。
支店別や担当者別の集計は、比較や判断の材料に使われます。一部の行が漏れると、その分類だけ少なく見えたまま話が進みます。
架空の例で比べると、次のとおりです。
| 場面 | Before | After |
|---|---|---|
| 合計が合わないとき | 思いつく所から見る | 範囲→条件→データの順に見る |
| 報告書に写す前 | そのまま写す | 分類ごとの合計を全体と比べる |
この表では、ずれに気づくのが報告書を出す前になっています。
範囲・条件・データの順に見ると、合わない理由を一から探す時間が減ります。検算を写す前の手順に入れると、報告書の数字を出し直す手間も減ります。
毎月の集計そのものを、写す作業の少ない形にする手順は、次の記事で整理しています。
まとめ
SUMIFS・COUNTIFSの合計・件数が合わないときは、範囲(1〜3)→条件(4〜6)→データ(7・8)の順に確認します。
- 合計範囲と条件範囲の大きさが同じか
- 各範囲の開始行がそろっているか
- 1番目の引数が合計範囲になっているか
- 比較の条件を「
"」と「&」で書けているか - 同じ列に両立しない2つの条件を並べていないか
- 条件に意図しない「*」「?」がないか
- 数値や日付が文字列で入っていないか
- 余分な空白や表記の違いがないか
それでも合わないときは、数式の途中・閉じたブック・数式のセルの位置を見ます。分類ごとの合計を全体と比べてから写すと、ずれた集計のまま報告することを防げます。
