ＳＵＭＩＦ関数の集計が上手くいきません。

質問

「集計」シートのＡ列に得意先コード・Ｏ列に集計欄
「貼付」シートのＥ列に得意先コード・Ｊ列に集計欄があります。
なお、「貼付」シートのＥ列は重複する場合もあります。

「集計」シートのＡ列の値を、「貼付」シートのＥ列から探して、その行のＪ列の数値の合計を
「集計」シートのＯ列に集計したいと思っています。
「集計」シートのＡ406が「Ｂ」で「貼付」シートのＥ200とＥ300が「Ｂ」で、「貼付」シートのＪ200が「150」
・Ｊ300が「250」であれば「集計」シートのＯ406に「500」（150+250）を表示させたいと思っています。
なお、「貼付」シートのＥ列には他に「Ｂ」の値がないものとします。

そこで、Ｏ列に次の関数を入れました。
「=SUMIF(貼付!E:E,A403,貼付!J:J)」

すると、（O4:O405）の範囲では、きちんと「貼付」シートの、各得意先コード別の合計が表示されるの
ですが、Ｏ406の値が「0」と表示されます。

数式は合っていると思うのですが、何が誤っているのか、わかるかたはいらっしゃいますか？
406行目がＡ列に得意先コードが入っている最終行であることも関係ありますか？

あるいは、どのように集計したら良いのか、教えていただけると助かります。

回答No.7

K Kazz（@JazzCorp）

"B"という検索値は、そちらのサンプルデータではたった２つ、そのセルで、文字列にカーソルを当てて、文字数を確かめてみてください。
文字列の後方もしくは前方の、空白、改行、その他表示不可文字の有無は直ぐに判ります。
もし、このカウントが１だとすると、それは、前方の空白、改行、その他表示不可文字の存在を意味します。
=COUNTIF($E$4:$E$406,"B"&"*")

最後、まだ判らないのは、検索できた筈の１つのデータが何故出てこないのか？
この数値データも怪しいですね？（非数値？）

補足

カウントしたら「1」でした。。。
エクセル自体がおかしいのですかね。
自動計算になっているのに、自動計算されません。

回答No.6

K Kazz（@JazzCorp）

簡単な検算をやってみてください。
今、問題になっている"B"という検索値が、全角なのか半角なのか単にモデル（サンプル）なのか判らないが、後方に空白が付いていると別物になる。
「貼付」シートの中、空いてる所で、

=COUNTIF($E$4:$E$406,"B")

Ｅ列にオートフィルタを設定して、リストの中を良く見てください。
"B"のヴァリエーションがないですか？

補足

「貼付」シートに「=COUNTIF($E$4:$E$406,"B")」を入れると同時に
他のセルに「=COUNTIF(集計!$A$3:$A$406,"B")」を入れました。

どちらも「1」が表示されました。
もう、私も驚いています。

今後、このように、読み込まれない数値があるのであれば、今回の事例であれば
「貼付」シートのＪ列の右のＫ列に、集計シートの対象となるコードの数値
「SUMIF(集計!A$2:A$600,E2,集計!O$2:O$600)」のような関数を入れて、
Ｌ列に、貼付シートの対象となる数値「SUMIF(E$2:E$600,E2,J$2:J$600)」
のような関数を入れて、Ｍ列にＫ列とＬ列の差異を出して「０」以外の数値を出すなどして、
問題となる部分を探していったほうが早そうな気がしてきました。

今回、なぜか値が正しく反映されない部分が１箇所と少なかったので、エラーを
発見して、手で修正していっても何とかなりそうな気がします。

オートフィルタで、問題となっているコードを探しましたが、1件しか見当たりませんでした。

回答No.5

K Kazz（@JazzCorp）

もう、これしか思いつかない!
「貼付」シートの参照範囲が固定されていない？
「O4」に、

「=SUMIF(貼付!$E$4:$E$406,$A4,貼付!$J$4:$J$406)」

以下、素直に引っ張ってコピー

補足

やってみましたが、結果が同じでした。
私もびっくりしています。

ちなみに「貼付」シートのＩ列にも値があり、
最後の「貼付!$J$4:$J$406」の部分を「貼付!$I$4:$I$406」に
変えると、Ｉ列の値が出てきてしまいます。

現在、そういったエラーが1件、しかもそれが最終行だったので、発見
しやすかったのですが、途中の行であれば、きちんと探し出せたかどうか。

やはり、こういったことはほとんど考えられないと思っておきます。

いろいろ考えていただき、ありがとうございました。

回答No.4

K Kazz（@JazzCorp）

この２つの違いは判りますね？
=SUMIF(貼付!E:E,$A406,貼付!J:J)」
=SUMIF(貼付!E4:E406,$A406,貼付!J4:J406)
データの検索範囲が違う。なので「貼付」シートの4～406行以外（1～3行に限らない）に、何かデータらしきものがあれば、計算結果に影響することになる。

ゴミ掃除？
かなり単純、
「貼付」シートをバックアップ、
4～406行をコピー、
「貼付」シート全体をイレイズ、
4～406行を書き戻す、

ところで、シートの再計算は自動ですね？

書き換えを勧めた結果も同じ？
=SUMIF(貼付!E4:E406,$A406,貼付!J4:J406)

補足

> 「貼付」シートをバックアップ、
> 4～406行をコピー、
> 「貼付」シート全体をイレイズ、
> 4～406行を書き戻す、
これもやってみましたが、結果はなぜか「O」が表示されます。

シートの再計算は自動で、400行目の正しく表示される書式をコピーしても
結果はなぜか「O」が表示されます。

> 書き換えを勧めた結果も同じ？
> =SUMIF(貼付!E4:E406,$A406,貼付!J4:J406)
範囲の行を設定しても、しなくても、$とつけてもつけなくても、結果は「0」が
表示されて、不思議に思っています。

今、いろいろエクセルをいじってまして、「A406」のコードを「貼付」シートの
Ｅ列のいろんな場所に貼り付けてみました。
すると、4-5行目に貼り付けた場合は「集計」シートの「O406」の値が変化しないのに
対して、6行目から下方向に貼り付けると、「集計」シートの「O406」の値が反映されます。

「=SUMIF(貼付!E4:E406,$A406,貼付!J4:J406)」の関数が
「=SUMIF(貼付!E4:E406,$A406,貼付!J6:J406)」として反映されているような感じです。

こういうことってありえますか？
関数を入力している範囲が多いとこのようなエラーが出るのでしょうか。

回答No.3

K Kazz（@JazzCorp）

「貼付」シートの1～3行にゴミがある？？（少なくとも「O406」の結果に影響している？）
試しに「O406」を書き直してみる。

=SUMIF(貼付!E4:E406,$A406,貼付!J4:J406)

ゴミ掃除が可能ならそれも方法！

補足

「ゴミ」とは具体的にどのようなものでしょうか。
重複データは「貼付」シートに無いことを確認済みです。

回答No.2

K Kazz（@JazzCorp）

推測するに、ROW()と式のA列のインデックスがズレてないですか？

「O406」：「=SUMIF(貼付!E:E,$A403,貼付!J:J)」？？？

（これだと、上方向にもズレてる気もするが...）

ま、仮に「O406」に正しい式「=SUMIF(貼付!E:E,$A406,貼付!J:J)」が入っていたとしても、正解は「400」！！

補足

失礼しました。

「O403」に「=SUMIF(貼付!E:E,$A403,貼付!J:J)」が入っていました。
けれども、「O403」には「400」が表示されずに「0」が表示されます。

「O402」には、予定通りに「貼付」シートＥ列が「A402」の値と同じものに
対する「貼付」シートＪ列の合計が表示されています。また「O401」から上の
セルに関しても正しそうです。

正しく関数を入力したつもりなのに、思っていた結果が得られずに、驚いています。

回答No.1

keithin

「例えば403の式」じゃなくて「問題のＯ４０６セルに実際に入っている式」をよく見て、それが
=SUMIF(貼付!E:E,A406,貼付!J:J)
だとすれば、A406に記入されている内容が間違っていると考えるのが妥当です。

A406で一回Deleteキーを押して間違った内容を消してしまい、改めて貼付シートのＥ列から該当するセルのデータをコピーして、A406に貼り付けてみましょう。

補足

失礼しました。

「O403」に「=SUMIF(貼付!E:E,$A403,貼付!J:J)」が入っていました。
けれども、「O403」には「400」が表示されずに「0」が表示されます。

「O402」には、予定通りに「貼付」シートＥ列が「A402」の値と同じものに
対する「貼付」シートＪ列の合計が表示されています。また「O401」から上の
セルに関しても正しそうです。

> A406で一回Deleteキーを押して間違った内容を消してしまい、改めて
> 貼付シートのＥ列から該当するセルのデータをコピーして、A406に貼り付けてみましょう。
「A403」について、一旦Deleteキーを押して、Ｅ列に該当するデータをコピーして
「A403」に貼り付けても「O403」の値は「0」のままです。

また「A400」や「A390」など、他のＡ列に入っているコードを「A403」に貼り付けた
場合は「O403」に期待している数字が表示されます。
このことから「O403」の関数か合っているものと思われます。

関数は正しいと思うのに、期待している数値が表示されないのが、疑問に思います。