みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル SUBTOTALとSUMIFの組み合わせ

条件付きで、フィルター時の合計を返したいとき、どのような数式を作ればいいのでしょうか?

現在、SUMIFS関数で、フィルターなしの状態の結果は出せているのですが、
フィルター時にも、それを反映して、返させたいと考えています。

例えば、A列に合計したいデータがあるとき、(1行目が項目)

=SUBTOTAL(9,A2:A100)
と入力すると、フィルター時でも合計を返してくれます。

ここにSUMIFS関数を組み合わせるには、どうすればよいのでしょうか?

宜しくお願いいたします。

バージョンは、エクセル2013RTです。

投稿日時 - 2014-09-25 13:57:38

QNo.8767839

困ってます

質問者が選んだベストアンサー

ANo.1です。
作業列を使いましょう。
添付の図ではE列を作業列として使用しています。
E2セルには↓の式を入れて下にコピーしてあります
=SUBTOTAL(103,A2)

これで、E列にはフィルタによって非表示されたかA列が空の行は0、フィルタで表示とされた行には1が入ることになります。
後はSUMIFSの条件に、E列の値=1である事を加ええれば良いでしょう。
=SUMIFS($D$2:$D$100,E2:E100,1,$B$2:$B$100,"XXX",$C$2:$C$100,"YYY")

投稿日時 - 2014-09-26 00:58:08

お礼

SUBTOTAL(103,
の使い方を初めて知りました。勉強になります。

大変使いやすい方法を教えて頂き、ありがとうございました。
また、機会がございましたら、ご教授お願い足します。

投稿日時 - 2014-09-26 09:22:50

ANo.5

このQ&Aは役に立ちましたか?

14人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

>2、私が作成していた、フィルター前の数式は以下のような形で、
=SUMIFS($D$2:$D$100,$B$2:$B$100,"●",$C$2:$C$100,"△")

もしこの数式でフィルタしない場合の集計値が表示できているなら、私の提示した数式をそのままコピー貼り付けすれば、A列でフィルタしたデータだけのSUMIFS関数に該当する数字が表示されるはずです。

うまくいかないとは具体的にどのような数字が返ってくるのでしょうか?

投稿日時 - 2014-09-25 23:26:49

お礼

私の●△部分の記入方法に勘違いがございました。申し訳ございません。
無事、きれいに返すことが出来ました。

本当に助かりました。ありがとうございます。
また機会がございましたら、ご教授お願いいたします。

投稿日時 - 2014-09-26 09:12:02

ANo.3

「何かの条件」で絞り込まれている状態で、C列に「a」と記入されているA列の数値のSUMIF:
=SUMPRODUCT((C2:C100="a")*SUBTOTAL(2,INDIRECT("A"&ROW(A2:A100)))*A2:A100)

投稿日時 - 2014-09-25 18:23:04

お礼

分かりやすい解説ありがとうございます。
無事に返すことができました。

大変勉強になりました。
また機会がございましたら、ご教授お願いいたします。

投稿日時 - 2014-09-26 10:20:20

ANo.2

SUMIFS関数の具体例が提示されていないので、回答しにくいのですが、たとえばB列に●、C列に△が入力されていることが集計条件でD列に集計対象の数字が入力されているなら、以下のような数式で上記の条件下でフィルタしたデータだけを対象としたD列の合計が出せます。

=SUMPRODUCT((SUBTOTAL(3,INDIRECT("a"&ROW($2:$100))))*($B$2:$B$100="●")*($C$2:$C$100="△"),$D$2:$D$100)

投稿日時 - 2014-09-25 15:32:05

補足

説明不足で申し訳ありません。

教えて頂きました、数式でうまく返すことができず、今一度アドバイスを頂いてもよろしいでしょうか?

=SUMPRODUCT((SUBTOTAL(3,INDIRECT("a"&ROW($2:$100))))*($B$2:$B$100="●")*($C$2:$C$100="△"),$D$2:$D$100)

1、かっことじの場所はこのままで、大丈夫なのでしょうか?
2、私が作成していた、フィルター前の数式は以下のような形で、
=SUMIFS($D$2:$D$100,$B$2:$B$100,"●",$C$2:$C$100,"△")

後半部分は、「,」で区切られているものを「=」に変換するような形でかまわないのでしょうか?

また、以下のようなデータを扱っています。
A列にSUMIFS関数に入れずにフィルターで操作したいデータ(例:支店名)
B列とC列にSUMIFS関数で絞るデータ(例:シリーズ名、商品名)
D列に合計したいデータ(例:金額)


よろしければご回答お待ちしております。

投稿日時 - 2014-09-25 22:59:50

ANo.1

単に、SUMIFSの検索条件にフィルターの条件を加えるだけで良いのでは?
質問の意味を勘違いしているかも知れませんので、具体例でも挙げて頂ければもう少しお望みに近い回答が出来るかと思います。

投稿日時 - 2014-09-25 14:55:41

補足

ありがとうございます。説明不足で申し訳ございません。
SUMIFSに加えても良いのですが、その対象データの種類が30以上ございまして、ここだけフィルターで操作したいと考えています。

以下のような形です。

A列にSUMIFS関数に入れずにフィルターで操作したいデータ(例:支店名)
B列とC列にSUMIFS関数で絞るデータ(例:シリーズ名、商品名)
D列に合計したいデータ(例:金額)

作成しているSUMIFS関数が、
=SUMIFS($D$2:$D$100,$B$2:$B$100,"XXX",$C$2:$C$100,"YYY")
ここから、フィルター時にも対応できる数式を作成したいと考えております。

投稿日時 - 2014-09-25 22:39:44

あなたにオススメの質問