SUMIFでの判定条件が異なるために正確に計算されない

よろしくお願いします。

=IF(A2="","",SUMIF(al_25!B:B,IF(LEN(al_25!B2)=9,RIGHT(al_25!B2,4),A2),al_25!C:C))

上記の式につきまして、
al_25!B:Bの列は商品番号の列で書式は標準、内容は「4桁までの数字」と「0000-0000」形式（0は数字）があります。ちなみにal_25!C:Cの列は販売個数でこちらの書式も標準です。

商品番号は、たとえば下記の番号であれば「5526」が子番になり、「4485」は親番になります。
5526
4485-5526

先にお伝えしました式では「0000-0000」形式の値が抽出されません。
たとえば、昨年度、商品番号「5526」が5ヶ販売実績があり、「4485-5526」は23ヶ販売実績があるとすると、子番の5526の販売合計を28ヶとなるようにしたく考えています。

上記につきましてご教授いただけますと幸いです。
どうぞよろしくお願いいたします。

質問者からの補足コメント

• 皆さま、ご助言ありがとうございます！
  すべての回答をまだ見れていませんが、No1さんのアドバイスを見て衝撃が走りました。。。
  
  そうです、単に列を増やし、=RIGHT(任意のセル, 4)だけで良かったです。
  そして、SUMIFの指定範囲を変更するだけで。。。
  
  エクセル関数、かつてはwebを見て自分なりに色々考えたり、うまくいかなければ式を細分化してひとつひとつの値を確認したりしていたのですが、最近は完全にAIに頼り、怠慢になって、今回、そのAIの助言がまったくうまくいかずムキになって大切な基本を完全に忘れておりました。2時間くらいはAIと押し問答を繰り返していたと思います;w　急がば回れ、が身に沁みました。
  
  他、皆さまからいただいた式を試してみたく、また理解したく思いますが、
  取り急ぎの御礼までにて失礼いたします！

    補足日時：2024/02/12 12:31

No.1 ベストアンサー 回答者： angkor_h 回答日時： 2024/02/11 17:56

関数の組み合せの場合は、

個々の関数結果を示す行を設けて、更にそれを次の関数で処理する、
と言う繰り返しをしたほうが、解決が速いです。

この回答へのお礼

ご回答ありがとうございました！
目が覚めました！

お礼日時：2024/02/12 13:27

No.6 回答者： fujillin 回答日時： 2024/02/12 15:41

No5です。

連投失礼。
投稿してから、範囲指定が合っていなかったことに気が付きました。

最初の方の式を訂正しておきます。
正しくは以下です。
（2～20行目までを計算対象に指定）
=IF(A2="","",SUMPRODUCT((RIGHT(al_25!B2:B20,4)=A2&"")*al_25!C2:C20))

この回答へのお礼

お教えいただきました式で正確な結果が出ました！

ただ、単にコピペしたのと、行数を2000行にしただけですので、理解が危ういです。お金もないのに高級自動車に乗るようなものです。ですので、現状はしっかりと理解できている身の丈に合った方法で行いたいと思っています。

>1行目がタイトル行等になって

1行目、項目が入っていました。
予めお伝えすべき点だったのに誠に申し訳ございませんでした。

>また、範囲を指定して計算することで、（列全体で計算するよりも）計算負荷を小さくすることができます。

そうなのですね。
私はてっきり最終行の値は、VBAのEnd(xlUp)みたく自動で判定してくれているのだと思っていました・・・

>=IF(A2="","",SUMPRODUCT(AGGRE

こちらの式に関しましては、、、
すみません、まったくお手上げで、私が手を出したらいけない式です。

追加のご助言をいただき、誠にありがとうございました！

お礼日時：2024/02/12 16:11

No.5 回答者： fujillin 回答日時： 2024/02/12 15:37

No4です。

＞お教えいただきました関数をためしてみましたが、#VALUE!　が返されました。
多分、C列に数値以外のものが存在するのではないかと想像します。
（乗算を行っているので、数値化できないものがあればエラーになります）
ご質問文には記載がありませんが、1行目がタイトル行等になっていて、C列に文字列が入力されていたりはしませんか？

No4では（元の式がそうだったので）列全体での計算式にしてありますが、C列に数値以外のものがあるのなら、
=IF(A2="","",SUMPRODUCT((RIGHT(al_25!B2:B10,4)=A2&"")*al_25!C2:C20))
のように、対象となる範囲だけを指定するようにすれば算出できるはずです。
（空白セルは含めてもよいので、広めに範囲設定しておくことは可能です）
また、範囲を指定して計算することで、（列全体で計算するよりも）計算負荷を小さくすることができます。


どうしても、列全体を対象にしたければ、関数で数値以外を除くことも可能ではありますが、無駄な計算が大幅に増加するので重くなるのと、計算式が長くなるので視認性も悪くなります。
=IF(A2="","",SUMPRODUCT(AGGREGATE(15,6,(RIGHT(al_25!B:B,4)=A2&"")*al_25!C:C/(al_25!C:C<>""),ROW(OFFSET(A1,0,0,COUNT(al_25!C:C))))))

No.4 回答者： fujillin 回答日時： 2024/02/11 18:59

こんばんは。

ん～～・・、何をしたいのかよくわかりませんけれど・・
B列の値から該当するものを抽出して、そのC列の値の合計を求めたいってことと解釈しましたが・・

その割には、
＞IF(LEN(al_25!B2)=9,RIGHT(al_25!B2,4),A2)
B2セルしか参照していませんけれど、B2セルが9文字ある場合は必ず検索キーはB2セルの後ろ4文字になりますけれど、そういうことをしたいのでしょうか？
ご提示の式では、B2セルが仮に「4485-5500」の場合には、A2セルの値が何であろうとも、検索キーは「5500」になると思いますけれど・・？


検索キーはあくまでもA2セルの値であって、B列がそのまま一致する場合と後ろ4文字が一致する場合と両方とも抽出したいってことではないのでしょうか？
例えば、結果を表示するシートのA2セルに「5526」がある場合に、B列が「5526」の場合も「4485-5526」の場合も両方抽出して合計したいってことではないかと思ったのですけれど・・
もしも、上記の推測で良いのなら、
=IF(A2="","",SUMPRODUCT((RIGHT(al_25!B:B,4)=A2&"")*al_25!C:C))
とかで、できませんか？


※ ご質問内容の解釈が違っている場合は、見当はずれなのでスルーしてください。

この回答へのお礼

お教えいただきました関数をためしてみましたが、#VALUE!　が返されました。私のお伝えすべきところが誤っているのは明確ですが、SUMPRODUCTというものを見、今、やや遠いところを眺めています。

>検索キーは「5500」になると思いますけれど・・？

そうですね；　そこからが間違っていたのだと思われます。ちょっと頭を冷やしてから、お教えいただいたコードのひとつひとつを手繰って理解していければと思います。

ご回答ありがとうございました！

お礼日時：2024/02/12 14:13

No.3 回答者： zongai 回答日時： 2024/02/11 18:31

合計するための子番号「5526」はA2を参照しているということでしょうか？

そしてA2は数値なのか文字列なのか・・・

A2の数値を小番号として参照していると仮定して、

=SUM(SUMIFS(al_25!C:C,al_25!B:B,A2),SUMIFS(al_25!C:C,al_25!B:B,"*-"&TEXT(A2,"0000")))

こういう式ではどうでしょう？

この回答へのお礼

ご教授いただきました関数を試させていただきましたところ、正確な値が抽出されました！

ただ、今回はシンプルな方法でいこうと思います。しっかり理解できていなければまたこういう事態に陥りそうですので。。。；ｗ

この度はご回答誠にありがとうございました！

お礼日時：2024/02/12 14:03

No.2 回答者： neKo_deux 回答日時： 2024/02/11 18:08

> 上記の式につきまして、

シートとか無いから、よくわからんですが。


子番の「5526」だけ抽出する作業列を作って、その作業列に対してSUMIFするのが良いと思う。

A　　　　　　　　　　Ｂ　　　　　Ｃ
　　　　　５５２６　　５５２６　　5
４４８５－５５２６　　５５２６　　23

B1:=RIGHT(A1,4)とか。
で、
=SUMIF(B1:B2, "=５５２６", C1:C2)
とか。

この回答へのお礼

No1さんの回答を朝に見、すぐにこれだ！と思い、関数を分割しました。
後で他の方の回答を見させていただいたところNo2さんの仰られる方法でした；ｗ

ちょっと列は変えましたが（あまり必要でなかったA列を削除、C列に4桁抜き出した商品番号列を追加）、集計のシートは下記のようで正確に値が抽出されました。

=IF(A2="","",SUMIF(al_25!C:C,A2,al_25!B:B))

ご回答ありがとうございました！

お礼日時：2024/02/12 13:37