いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)SUMIF関数の集計がうまくいかない原因

 GoogleスプレッドシートでSUMIF関数を使って合計を求めるときに、結果が0になったり計算の意図とは異なる値になる主な原因とその対策をいくつか紹介します。多くはCOUNTIF関数での失敗例と共通しますがSUMIF関数ならではの原因もあります。

  • 日付を条件にするとき" "で囲んでいない、比較演算子が誤っている、複数列を合計しようとしている、などのケースによりSUMIFの結果が誤ったものになります。

原因と対策

似たような別の文字を条件にしている

 検索しようとする文字と見た目がほとんど同じ別の文字を条件に指定しているケースです。
 画像では検索範囲のB列にマルのような文字が記されているのでSUMIF関数の第2引数(条件)として記号の○(マル)を指定しています。しかし実際にB列に記録されているのは漢数字の〇(ゼロ)ですので正しい合計が求められていません。



 式中の条件値を漢数字に直すことで正しい合計が求められました。

日付を条件にするとき""で囲んでいない

 画像では「2023/3/10」という日付を条件に指定しようとしており、SUMIF関数の第2引数を「2023/3/10」としています。
 一見よさそうに思われるものの、これは「2023割る3割る10」(=67.433…)という数値とみなされるのでうまくいきません。



 このときは" "(ダブルクォーテーション)で囲んで「"2023/3/10"」とすることで合計が求められます。
 文字列として比較しているようにも見えますが、書式が異なる日付(「2023-3-10」など)に対してもうまくいきます。また、「">=2023/3/10"」というように大小比較もできます。

文字(書式なしテキスト)と数値を比較している

 文字(書式なしテキスト)として記録されている数字を比較演算子(>=や<など)を使って比較しようとしているケースです。セル内の文字が右寄せされているとさらに紛らわしくなります。
 画像では第2引数を「">=100"」とし、B列の100以上の数に対応するC列の数値を合計しようとしていますがうまくいきません。



 B列(B3:B7)のセルの書式を変更する方法もありますが、ここでは式の方で対応しています。
 具体的にはVALUE関数で数字を数値に変換しています。ただしB3:B7の範囲を一括で変換するため、さらにARRAYFORMULA関数で囲む必要があります。

 E3セル

=SUMIF(ARRAYFORMULA(VALUE(B3:B7)),">=100",C3:C7)

比較演算子が誤っている

 画像ではB列の100以上の数に対応するC列の値を合計しようとしています。そこで第2引数を「"=>100"」としていますが、これは比較演算子として無効な表現なのでうまくいきません。



 正しい比較演算子を使って「">=100"」とすればうまくいきます。
 同様に100以下を指定する場合に「"=<100"」とするのも誤りで、正しくは「"<=100"」です。

セル参照を" "の内側に記している

 E3セルと等しい値(100)を条件にしようとしていますが、「"E3"」としているため「E3」という文字と解釈され、うまく合計が求められていません。



 これは単に「E3」とすれば解決します。



 こちらではセル内の数値と比較しようとしています(E3の値以上であることを条件にする)が、やはりセル番地を" "内に入れているためうまくいきません。



 ここではE3を""の外側に置き&でつなぎ、「">="&E3」と、すればうまくいきます。

比較対象を比較演算子の左側に置いている

 ここではB列の100以下の数に対応するC列の数値を合計するつもりで第2引数を「"100>="」としています。
 比較演算子は間違っていないものの、これではうまくいきません。



 比較対象とする値は比較演算子の右側に置き、比較演算子もそれに合わせる必要があります。
 そこで「"<=100"」と変えることで正しい合計が求められました。

検索範囲と合計範囲を逆に指定している

 検索範囲(条件を課すセル範囲)と合計範囲を逆に指定してしまっているケースです。
 一見バカバカしいですが、SUMIFS関数の場合は第1引数が合計範囲、第2引数が条件範囲というように指定順序が逆になるので覚えにくく、案外やってしまいがちです。

 正しい結果は2番目の例と同じですので省略します(次の例についても同じ)。

検索範囲と合計範囲がずれている/サイズが異なる

 検索範囲(第1引数)と合計範囲(第3引数)が上下にずれていたり、サイズ(行数)が異なるケースです。後者の場合(FILTER関数のように)エラーになりそうな気もしますが、実はエラーにはならず意図しない数値が返ります。
 画像では検索範囲を「B2:B7」と見出し行を含めて指定してしまっています。そして条件値である「2023/3/10」が検索範囲中の上から2,3,5行目にあるため、合計範囲(C3:C7)中の2,3,5行目の数値つまり2,4,16の合計(22)が求められています。これは計算の意図とは異なる誤った結果です。

複数列の数値を合計しようとしている

 SUMIF関数で複数列の数値の合計を求めることは可能ですが、その場合は検索範囲(第1引数)にも複数列を指定する必要があります。
 しかし画像では検索範囲が1列(B3:B7)なのに合計範囲に2列(C3:D7)を指定しています。
 結果的にC列の数値だけが合計され、意図しない結果となっています。



 この場合「=SUMIF({B3:B7,B3:B7},"2023/3/10",C3:D7)」として検索範囲をムリヤリ2列にしてしまえば2列の合計が求められます(画像は省略します)が、FILTER関数を使うことも考えられます。
 次の画像ではFILTER関数を使ってC,D列のうち条件に該当する行(B列の「2023/3/10」に対応する行)を抽出し、SUM関数で合計を求めています。

 F3セル

=SUM(FILTER(C3:D7,B3:B7=DATEVALUE("2023/3/10")))

 合計する列が多くなっても指定が面倒にならないことと、FILTER関数の第1引数で配列記法「{範囲1,範囲2}」を使えば飛び飛びの列を合計することも可能(SUMIF関数の合計範囲ではこのような指定ができないので困難)なのがメリットです。
 なお日付の指定方法がSUMIFとは異なるのが注意点です。