【Excel】sumifs関数で合計対象範囲も複数列から条件指定したい

sumifsは複数条件に絞って、縦合計を出せますよね。

ただ、合計対象範囲が複数列ある場合、集計する方法がありますか？

例えば、横方向の列に、
1組、2組、3組、4組～20組とあって、
複数条件を満たす、3組の合計は？ とか20組の合計は？とか列の位置の条件もあるのです。
分かりづらくて申し訳ありませんが、よろしくお願いいたします。

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

• 文章だけでは伝わらず失礼しました。
  現物の表を貼るのは差し支えるので、サンプル表の画像を添付します。
  
  "4月"　"A社"　"①"　"A氏"　の「3組」の合計値を出す式が知りたいです。
  
  別シートに、必要箇所だけのサマリー表を作る必要があり、
  添付の表の中の数値を入力していくにつれてサマリー表が自動更新できるようにしたいのです。

  
    補足日時：2021/06/29 18:37

No.10 回答者： banzaiA 回答日時： 2021/07/03 12:04

=SUM(INDEX(FILTER($A$2:$I$16,($A$2:$A$16="6月")*($B$2:$B$16="B社")*($C$2:$C$16="①")*($D$2:$D$16="E氏"),""),,MATCH("３組",$A$1:$I$1,0)))

上の式の
6月
B社
①
E氏
３組
の部分をご希望により書き直せば（またはセル指定も可能）合計はでます。

No.9 回答者： goomania 回答日時： 2021/07/03 12:00

No.5、６、７です。

>そのやり方に沿って数式を作ってみましたがやはり「#VALUE!」エラーが解消できません。。。
>その具体図を貼れてませんが、数式から間違いを判断できないでしょうか？
>=SUMPRODUCT((F3:P1003)*(A3:A1003=R4)*(B3:B1003=S4)*(D3:D1003=T4)*(F2:P2=U4))
>F3:P1003に数値が入っていて、F2:P2が項目名の範囲です。

上記のような症状の原因として考えられるのは、ご質問者が「F3:P1003に数値が入っていて」とおっしゃっている、F3:P1003の範囲に数値でないもの、例えば数式で表示している「空白(="")」が含まれているという可能性です。
「F3:P1003に数値が入っていて」とおっしゃっているのので、数値でなければ強制的に「0」として計算対象外にしてやればOKです。
数式を以下のように修正します。

{=SUMPRODUCT(IF(ISNUMBER(F3:P1003),F3:P1003,0)*(A3:A1003=R4)*(B3:B1003=S4)*(D3:D1003=T4)*(F2:P2=U4))}

ただし、上記数式は365以外のバージョンでは配列数式(CSE数式)として確定する必要があります。{}を除く数式を入力して、Ctrl+Shift+Enterで確定させてください。

No.8 回答者： banzaiA 回答日時： 2021/07/03 10:01

ExcelのバージョンOffice365ですが、

filter 関数　match 関数　index 関数　sum関数
でできると思います。
Excelのバージョンは何ですか。

この回答へのお礼

同じくOffice365です。

お礼日時：2021/07/03 10:57

No.7 回答者： goomania 回答日時： 2021/07/02 21:22

No.5、６です。

>"C"の役割が理解できませんでした。C列で何をしているんだろう？と。
>解説頂けると助かります。

INDIRECT関数は、INDIRECT(参照文字列, [参照形式])という形で使用しますが、[参照形式]では、FALSE を指定すると、参照文字列には R1C1 形式のセル参照が入力されていると見なされます。
ご質問者が示された
=SUMIFS(INDIRECT("C"&MATCH(U4,A2:P2,0),0),A:A,R4,B:B,S4,D:D,T4)
という数式の中で、INDIRECT("C"&MATCH(U4,A2:P2,0),0)
という形でINDIRECT関数を使用しており、参照形式は「０」つまり、FALSEです。従って参照文字列はR1C1形式ということになります。
Wikipediaによると、
(前略)Microsoftの表計算ソフト「Multiplan」のセル位置の指定はR1C1参照形式（行をR1, R2, R3…、桁をC1, C2, C3…で表す）が採用された。
8ビットパーソナルコンピュータでベストセラーとなったVisiCalcや、MS-DOS用としてベストセラーとなったLotus 1-2-3はA1参照形式（行を1, 2, 3…、桁をA, B, C…で表す）であり、ビル・ゲイツはそれを「戦艦方式」と称して嫌っていたためにこの方式が採用された。
しかし利用者の多くはA1参照形式に慣れており、R1C1参照形式は判りづらいと評された。
そのため、後継ソフトであるMicrosoft Excelでは初期状態がA1参照形式となり、利用者が好みに応じてR1C1参照形式に切り替えることができるようになった。(後略)
と解説されています。
つまり、上記数式の"C"はC列という意味ではなく、R1C1参照形式の"C"つまり「列(column)」そのものを表しています。
例えば、6行目すべてを集計する場合「=SUM(6:6)」という数式でOKですが、8列目すべてを集計する場合「=SUM(H:H)」と記述する必要があり、列番号が判明しても英文字に変換しなければなりません。
MATCH(U4,A2:P2,0),0)でU4に合致するA2～P2のセル内容を検索し、合致した列番号が取得できます。取得した列番号が「8」だとして、英文字の「H」に変換するのは大変です。
そこで、INDIRECT関数を使って、INDIRECT("C8",0)とSUM関数を組み合わせて「=SUM(INDIRECT("C8",0))」とすれば、「=SUM(H:H)」と同じ意味を実現できます。
ここまでの説明でお判りのとおり、「SUMIFS関数で合計する範囲をR1C1形式を使って列番号で指定できるようにしている」というのがINDIRECT関数で"C"が登場する理由です。

この回答へのお礼

今まであいまいだった部分が分かりました。
ありがとうございました。

お礼日時：2021/07/03 15:10

No.6 回答者： goomania 回答日時： 2021/07/01 03:00

No.5です。

>sumifs内の各行数がずれていると駄目という事を調べたので
>offsetの範囲の縦幅、各条件の範囲を同一にしたつもりですが･･･
ご認識のとおりです。
銀鱗さんの回答に従って、SUMIFS関数とOFFSET関数を組合わせるなら、OFFSETの範囲の縦幅、各条件の範囲を同一にする必要があります。
添付画像①をご覧ください。L9に
=SUMIFS(OFFSET(D1,0,MATCH(L6,E1:I1,0),16),A:A,L2,B:B,L3,C:C,L4,D:D,L5)
という数式を記述していますが、結果は「#VALUE!」となっています。
OFFSET関数で縦16行の高さを指定しているのに、A列～D列まで行数の指定がなく、各列全体になっています。ここで不一致が発生している訳です。
これを修正して添付画像②のようにL10に
=SUMIFS(OFFSET(D1,0,MATCH(L6,E1:I1,0),16),A1:A16,L2,B1:B16,L3,C1:C16,L4,D1:D16,L5)
という数式を記述すると、正しく結果が表示されています。
なお、OFFSET関数ではなく、INDEX関数と組み合わせたものが、添付画像③L11の
=SUMIFS(INDEX(E2:I16,,MATCH(L6,E1:I1,0)),A2:A16,L2,B2:B16,L3,C2:C16,L4,D2:D16,L5)
という数式です。
A列～D列まで行数の指定をせず、列全体にするとメモリの無駄遣い、計算時間の浪費という問題があるのでお勧めしませんが、
数式としては、添付画像④L12の
=SUMIFS(INDIRECT("C"&MATCH(L6,A1:I1,0),0),A:A,L2,B:B,L3,C:C,L4,D:D,L5)
という数式にすれば、A列～D列の行数指定なしでも結果が返ります。

この回答へのお礼

No.5のお礼のところでエラーになる式を挙げましたが、
その後、No.6を読みながら結果的に下記数式でうまく行きました。

=SUMIFS(INDIRECT("C"&MATCH(U4,A2:P2,0),0),A:A,R4,B:B,S4,D:D,T4)

F3:P1003に数値が入っていて、F2:P2が項目名の範囲です。
A3:A1003、B3:B1003、D3:D1003に条件の値が入っていて、
R4、S4、T4、U4に検索値が入っています。

うまく行ったのですが、
"C"の役割が理解できませんでした。C列で何をしているんだろう？と。
ただ、計算結果は合っているようなのです。
解説頂けると助かります。

お礼日時：2021/07/02 17:40

No.5 回答者： goomania 回答日時： 2021/06/30 23:46

SUMIFS関数とOFFSET関数の組み合わせという回答が複数あるようですが、No.2さんのSUMPRODUCT関数の利用を支持します。

添付画像をご覧ください。
A列からI列まで、ご質問者が例示したデータを作成してあります。
K2セル～L7に集計条件と集計数式を記述しています。
L7に記述している数式は
=SUMPRODUCT((E2:I16)*(A2:A16=L2)*(B2:B16=L3)*(C2:C16=L4)*(D2:D16=L5)*(E1:I1=L6))
となります。
この数式に登場する複数の「セル範囲」に名前を定義した数式をM7に記述しています。
=SUMPRODUCT(実績値*(月=L2)*(社名=L3)*(部門=L4)*(担当=L5)*(組=L6))
名前を定義すると数式は上記のようになり、非常に判り易いものになると思います。
ご質問者は
>添付の表の中の数値を入力していくにつれてサマリー表が自動更新できるようにしたい
とおっしゃっているので、L2～L6の集計条件を必要な数だけ用意すれば、自動的にサマリーが作成されるということになります。
※上記数式は集計条件をセルで指定していますが、当然のことながら数式の中に集計条件を直接記述してもOKです。
※各「セル範囲」は実際のデータの範囲に合わせて範囲指定・名前の定義を行ってください。

この回答へのお礼

ありがとうございます。
そのやり方に沿って数式を作ってみましたがやはり「#VALUE!」エラーが解消できません。。。

例示したものとは、少しレイアウトが変わってしまっていて、
その具体図を貼れてませんが、数式から間違いを判断できないでしょうか？

=SUMPRODUCT((F3:P1003)*(A3:A1003=R4)*(B3:B1003=S4)*(D3:D1003=T4)*(F2:P2=U4))

F3:P1003に数値が入っていて、F2:P2が項目名の範囲です。
A3:A1003、B3:B1003、D3:D1003に条件の値が入っていて、

R4、S4、T4、U4に検索値が入っています。

これで「#VALUE!」エラーなんです・・・

お礼日時：2021/07/02 16:44

No.4 回答者： 銀鱗 回答日時： 2021/06/30 03:19

補足ありがとうございます。

これなら具体的なやり方を説明できます。

・・・本題・・・

こういうものは一つずつ考えましょう。
まずは SUNIFS関数で、条件を作り、G列の合計を求める数式を作ります。
　=SUMIFS(G:G,A:A,A2,B:B,B2,C:C,C2,D:D,D2)
こんな感じになります。
何をやっているのか、よく確認してください。
（質問文と補足分に対してツッコミを入れている感じになってます）

このままではG列の「3組」の合計で固定されてしまいます。
ではどうすれば良いのか。
No.3の回答者さんがヒントを出してくださっています。
OFFSET関数で範囲を指定するのです。

OFFSET関数は
　OFFSET(基準セル,行方向,列方向,縦幅,横幅)
のように指定して使います。
　OFFSET(A1,0,6,16,1)
これで、G1セルからG16セルを範囲として指定することになります。
適宜数字を変えればよいわけです。
その数字を変えるために MATCH関数 を使うというわけ。
　MATCH(検査値,検査範囲,検査方法)
のように指定して使います。
　MATCH("3組",B1:I1,0)
これで "3組" に対する「6」を得られます。

・・・

はい。あとはこれらの関数を組み合わせて使いやすいように若干修正して数式を作るだけです。
難しくはありませんでしたよね。
もしも、この説明で分からないという事であれば、諦めてお隣の席の人にお願いすべきと思います。
（そこまで心配する必要はないかな）

この回答へのお礼

ありがとうございます。
一から試してみて、
・最初はsumifsのみ、合計列を固定
・offsetで列範囲が移動できる事
・matchで必要な列の場所が取得出来る事
理解できました。

ですが、すみません、、、
組み合わせて何とか式を作りましたが、#Valueエラーが発生して解消に至りませんでした。
sumifs内の各行数がずれていると駄目という事を調べたので
offsetの範囲の縦幅、各条件の範囲を同一にしたつもりですが、
解消できませんでした。情けないです。
隣の席の人にお願いしたいけど相談できるスキルの人は見当たりません(笑

作った式を見せられれば良かったのですが、事務所に置いてきてしまい本日は貼れません。
私が陥った落とし穴にピンときたら幸いです。。。

お礼日時：2021/06/30 20:47

No.3 回答者： chonami 回答日時： 2021/06/29 12:17

ざっくりの質問にはざっくりで回答しかできませんが、OFFSET関数で合計対象範囲を指定して列のずれはMATCH関数で指定とかではで

きませんかね？

No.2 回答者： fujillin 回答日時： 2021/06/29 12:16

こんにちは

SUMIF関数は「条件付き合計」なので、条件をうまく設定することが可能であれば、ご質問のような集計も可能と考えられます。
とは言っても、SUMIFで設定可能な条件はかなり単純な条件なので、少し複雑な条件になると難しいと思われます。

一方で、SUMPRODUCT関数などを利用すれば、多少複雑な条件であっても計算可能ですので、ご質問のようなケースでも算出することは可能になると推測します。
まぁ、限界はあるとは思いますので、条件によりけりとも言えますけれど・・・

No.1 回答者： 銀鱗 回答日時： 2021/06/29 12:04

ごめんよ。

分かりづらいというより、理解不能な質問になっています。

そういう時は表の例を図として添付すると良いでしょう。

・・・

んで、実際にどうしたいの？
3組を指定して合計を求める場合、その ”3組” はどうやって指定するの？
初めから数式で指定するの？
どこかのセルに ”3組” と入力するの？

やり方は多数あるんです。
曖昧な質問では、答えは一つにはなりませんよ。

この回答へのお礼

そうですよね。失礼しました。
具体例の図を示すようにします。

お礼日時：2021/06/29 17:31