SUMIFS 一部の条件のどちらかを参照する場合を教えてください。

集計の表の数量をすばやく計算したいのですが、品種・JANとコードが混じっています。
①支店別　✕　②取引先別　✕　③年月別　✕　④JANor品種コード別　の数量です。
実績表から数字を充てたいのです。
今まで、実績をソートしながら手打ちしていたようです。
これから３月までずっと手打ちはちょっといやなので・・
SUMIFSとSUMIFSと足せばいいのでは？聞いたのでやってみたのですが
（画像添付　例です　元の表は実績は別ファイルです。）
=SUMIFS($M$3:$M$16,$H$3:$H$16,A4,$I$3:$I$16,B4,$K$3:$K$16,C4)+SUMIFS($M$3:$M$16,$H$3:$H$16,A4,$I$3:$I$16,B4,$L$3:$L$16,C4)　長くなってしまいました。
条件は①~③まで一緒です。④だけ違うのですが　OR？が良く分からなくてエラーになってしまいます。
もっと　短くできる方法があれば教えて頂きたいです。

元の表は、他の列に別の情報・数値等があり、列は増やせない状況です。
（より詳しく知りたいものがJAN表記で、品種の中でひとつでもJAN表記する場合はその品種は全部JANにする決まりです）

どうぞよろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/04/26 14:08

こんにちは

ご提示の式で結果を算出できているのですよね？
それを短くしたいという意味のご質問と解釈しました。

とは言っても、たいして短くなってはいませんけれど・・
=SUMPRODUCT((H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3))*M$3:M$16)
では、いかがでしょうか？
（縦方向にしかフィルコピーしないものと判断し、列番号の$記号も除きました）

※ 画像がはっきりとしないので、セル位置等を間違えているかもしれませんがご容赦。
※ ご提示の式も同様ですが、万一JANコードと同じ品種番号が存在すると、意図とは異なる結果になります。
（桁数が全く違うようなので、そのようなことは無いとは思いますが・・）

この回答へのお礼

fujillin様　回答ありがとうございます。
上記式を入れてみましたが、＃VALUE！になってしまいました。
また次月の実績が追加になったときに、別の列に5月　6月をあるので年月も条件にいれたいのですがどこに条件を足せばよいのでしょうか。。。

お礼日時：2022/04/26 14:30

No.2 回答者： fujillin 回答日時： 2022/04/26 15:34

No1です

＞上記式を入れてみましたが、＃VALUE！になってしまいました。
めったにエラーになる式ではないのですが・・・

もしかすると、M列に数字以外の文字などが入っていませんか？
（ご提示の図の「・・・」が文字なのかな？）
No1の式にM列の値が数値であるかの判断を付け加えても良いのですが、数式が長くなってしまうので、効果がほぼなくなってしまうかもですね・・・
M列だけは「データの無いセルは空白」とすることはできませんか？
（そうすれば、エラーなく計算できるものと想像します）

配列数式にしてしまってもかまわなければ、
=SUM(IF((H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3)),M$3:M$16,0))
に変えることで、文字列が含まれていても計算が可能になります。
ただし、こちらの式の場合は、入力時にCtrl+Shift+Enterで確定が必要です。
（正しく入力されると、数式バーの表示が{　}付きの式に変わります）

あるいは、もしも、Office365環境をご利用なら、
=SUM(FILTER(M$3:M$16,(H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3)),0))
とすることで、M列に文字列があってもそれを除いて計算することが可能です。


＞別の列に5月　6月をあるので年月も条件にいれたいのですがどこに条件を足せばよいのでしょうか。。
ご覧になればわかると思いますが、式が変わっても、条件の部分はみな同じ計算をしています。
個々の条件式
　・(H$3:H$16=A3)
　・(I$3:I$16=B3)
　　・・・・
などの意味はわかると思いますが、これらをANDで判定したいときは乗算を、ORで判定する時は加算することで、結果が0または1になるようにしています。
（真偽値を四則演算させると、TRUEは1に、FALSEは0に変換されますので）
ただし、ORの条件が両方同時に成立するような内容だと、加算結果が2になってしまうので注意が必要です。
（これが、No1で「JANコードと同じ品種番号が存在すると・・」の場合に該当します）

上の例示の式の、条件の部分をAND、ORを用いて書き直してみると
　AND(H$3:H$16=A3,I$3:I$16=B3,OR(K$3:K$16=C3,L$3:L$16=C3))
となります。
（配列計算の場合、AND、ORの式にしてしまうと動作しなくなる場合がありますのでご注意）

もしも、条件を増減なさりたい場合は、この要領で加えたり減らしたりすれば良いです。

この回答へのお礼

fujillin様　続けての解説ありがとうございます。
・・・　を消してコードの列を数字のみにしたら、最初に提示いただいた式で計算できました！
（表が続いてる雰囲気をだそうと余計なことをしました　大変失礼いたしました　図も見ずらくてすみませんでした）
20人分の資料を手打ちしなくて済みます。。。
本当に助かりました。またお知恵をお借りすることがあるかもしれませんが、どうぞよろしくお願いいたします。

お礼日時：2022/04/26 16:20