フィルターかかってる時のＳＵＭＩＦ関数

フィルターかかってる部分だけのＳＵＭＩＦで
集計したいのがあるのですが普通にやるとフィルターかかってない部分まで集計されてしまいます。
何か良い式がありますか？

No.3 回答者： deus_ex_machina 回答日時： 2007/06/21 08:33

★まず、作業列でSUBTOTAL(3)関数を使い、"表示されているか否か"をマークします。

１行目　　：見出行
２～1000行：データ
Ａ列　　　：コード列や氏名列など“必ずデータが入力される、空白セルのない列”
Ｚ列　　　：作業列
とすると、
Ｚ２セル　：=SUBTOTAL(3,A2)
として、（フィルタを解除した状態で）末行までフィル。
フィルタした状態で表示されるセルには1,表示されないセルには0が入ります。
（もちろん見た目では判りませんが）

★次に、「表示されてて、しかも、指定した条件を満たす行」について合計します。

例えば、
SUMIFなら、
任意のセル：=SUMIF(B2:B1000,">300",C2:C1000)
と記述するような場合、

つまり、
検索範囲：Ｂ列
検索条件：">300"
合計範囲：Ｃ列
であれば、

任意のセル：=SUM(IF(Z2:Z1000=1,IF(B2:B1000>300,C2:C1000,0),0))
として、配列数式として入力(Ctrl+Shift+Enter)します。
「2～1000行目について、IF(Z?=1,IF(B?>300,C?,0),0)　の結果を足し上げよ」
という式です。

・AND関数，OR関数は使えませんから注意してください
・SUMIFではなく、SUM(IFです。念のため。

検索条件：Ｄ列
とする場合も同様に、
任意のセル：=SUM(IF(Z2:Z1000=1,IF(B2:B1000=D2:D1000,C2:C1000,0),0))
として、配列数式として入力(Ctrl+Shift+Enter)します。
「2～1000行目について、IF(Z?=1,IF(B?=D?,C?,0),0)　の結果を足し上げよ」
ですね。

なお、このサイトでは、複数条件での集計は
「配列数式ではなくSUMPRODUCT関数で行う」
「入れ子のIF文ではなく、算術演算子による論理演算を行う」
のが一般的な方法のようです。
SUMPRODUCT、論理演算の例については、過去にたくさん記述がありますので、そちらをご参考ください。

No.2 回答者： imogasi 回答日時： 2007/06/19 09:42

質問は２つの意味に取れて、あいまいです。

（１）フィルターで、絞られて、画面に出ている行全てのある列の数値の合計の場合
=SUBTOTAL(),C1:C6)
（２）フィルターで、絞られて、今画面に出ている行の中で、さらに別列で条件を掛けて、合計を求める場合
２条件（以上）の抜き出し合計の問題になるので、フィルタ解除した状態を頭において、SUMPRODUCT関数で複数条件による抜き出し合計を計算する。SUBTOTALIF的な関数は無い。
SUMPRODUCT関数の利用は過去質問にイヤというほど沢山あり、略。

No.1 回答者： maron--5 回答日時： 2007/06/19 06:01

◆抽出後の合計とついでに件数と連番

　　　　　A　　　　　　B　　　　　　C
1　　番号　▼　　区分　▼　　数量　▼
2　　　　　1　　　　　　a　　　　　　1
3　　　　　2　　　　　　a　　　　　　5
4　　　　　3　　　　　　b　　　　　　3
5　　　　　4　　　　　　c　　　　　　8
6　　　　　5　　　　　　c　　　　　　6
7　　　　　6　　　　　　a　　　　　　2
8　　　　　7　　　　　　b　　　　　　7
9　　　　　8　　　　　　c　　　　　　4
10　　　　　9　　　　　　b　　　　　　9
11　　　　　　　　　　　9　　　　　　45
　　　　↓↓　　区分を「b」でフィルター後
　　　　A　　　　　　　B　　　　　　C
1　　番号　▼　　区分　▼　　数量　▼
4　　　　　1　　　　　　b　　　　　　3
8　　　　　2　　　　　　b　　　　　　7
10　　　　　3　　　　　　b　　　　　　9
11　　　　　　　　　　　3　　　　　　19

合計　C11=SUBTOTAL(9,C2:C11)
件数　B11=SUBTOTAL(3,B2:B10)
連番　C2=SUBTOTAL(3,B$1:B1)　　※C10まで下にコピー