エクセル ＳＵＢＴＯＴＡＬとＳＵＭIFの組み合わせ

質問

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

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

例えば、Ａ列に合計したいデータがあるとき、（１行目が項目）

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

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

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

バージョンは、エクセル２０１３ＲＴです。

回答No.5

mt2008 · ベストアンサー

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")

お礼

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

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

回答No.4

MackyNo1

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

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

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

お礼

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

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

回答No.3

keithin

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

お礼

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

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

回答No.2

MackyNo1

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

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

補足

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

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

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

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

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

また、以下のようなデータを扱っています。
Ａ列にＳＵＭＩＦS関数に入れずにフィルターで操作したいデータ（例：支店名）
B列とＣ列にＳＵＭＩＦＳ関数で絞るデータ（例：シリーズ名、商品名）
Ｄ列に合計したいデータ（例：金額）

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

回答No.1

mt2008

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

補足

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

以下のような形です。

Ａ列にＳＵＭＩＦS関数に入れずにフィルターで操作したいデータ（例：支店名）
B列とＣ列にＳＵＭＩＦＳ関数で絞るデータ（例：シリーズ名、商品名）
Ｄ列に合計したいデータ（例：金額）

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