Excel 範囲内で条件を満たす異なる列の合計

質問

範囲内で条件を満たすセルで、異なる列の値を合計したい。

A1からA5に{10,20,30,40,50}という値が
B1からB5に{0,1,2,3,4}という値が
C1からC5に{5,6,7,8,9}入っている事とします。

A1:A5の範囲で複数の条件をつけます。

B列ではA列のセルの値が15<A<35となる、Ａ2とＡ3と同じ行であるB2とB3の値
C列ではA列のセルの値が25<A<45となる、A3とA4と同じ行であるC3とC4の値
全部でB2,B3,C3,C4のセルの値を合計を返すセルを作成したい。
この場合目的のセルが返す値は1+2+7+8で18となる。

補助行を作ってでも構いませんので、このような加算が可能とするセルの作り方はないでしょうか。
Excel2000を使用しています。どうかよろしくお願いします。

回答No.1

DIooggooID · ベストアンサー

まず、

> B列ではA列のセルの値が15<A<35となる、Ａ2とＡ3と同じ行であるB2とB3の値

は、・・・

　　=SUM(IF(A1:A5>15,IF(A1:A5<35,B1:B5,0),0))

つぎに、

> C列ではA列のセルの値が25<A<45となる、A3とA4と同じ行であるC3とC4の値

　　=SUM(IF(A1:A5>25,IF(A1:A5<45,C1:C5,0),0))

　いずれも　配列式　ですから、　数式を入力して確定するときに、単なる　Enterでなく

Ctrl　＋　Shift　＋　Enter　　の　三キーを同時打鍵して確定してください。

　この二つのセルの和を取れば、ご希望する値になります。

参考URL：

http://pc.nikkeibp.co.jp/pc21/special/hr/hr1.shtml

お礼

回答ありがとうございます。
この数式を作成して、それを合計するようなセルを作る事でうまくいきそうです。

回答No.5

MackyNo1

>説明しやすいようにＢ列とＣ列だけにしましたが、本来もっと多くの列があるという説明を忘れていました。

実際のデータのレイアウトを提示されたほうが的確な回答が得られると思います。

>200列近くはあるので回答頂いた数式をそのまま使うと非常に長くなってしまうのですが、

２００列あるデータでそれぞれ検索条件が違うのでしょうか？

この場合は、条件にもよりますが、基本的に条件ごとの数式を記載する必要がありますので数式はながくならざるを得ません。

ただし、添付画像のように検索条件の一覧がどこかのセルに入力してあるような場合は、数式でそのセル範囲を参照すれば短い数式で、すべての組み合わせの合計を出すこともできます（B列からG列までの数式）。

=SUMPRODUCT((A1:A5>B7:G7)*(A1:A5<B8:G8)*B1:G5)

お礼

回答ありがとうございます。

＞実際のデータのレイアウト
今データの入っていないＰＣで入力しているので載せる事ができなくてすみません。

条件付けに使う数式は列毎に違う値が出るようにCOLUMN()を使った物を使います。
拝見させていただいた添付画像のように検索条件の入力したセルを用意しようとすると
B7に(COLUMN()*10-5)、B8に(COLUMN()*10+15)という式を入力します。
検索条件の値の入力されたセルを作成して、それを使ったSUMPRODUCT()式にしたほうが
手間がかからなそうです。ありがとうございます。

回答No.4

tom04

こんにちは！
横からお邪魔します。

他の方のお礼欄に
＞200列近くはあるので・・・
とありますので、一つの案です。

↓の画像のように別Sheetにまとめてみてはどうでしょうか？
Sheet2のB・C列にSheet1の各列の○より大きく・△より小さい　の数値を表示させておきます。

画像のSheet2のD2セルに
=IF(COUNTBLANK(B2:C2),"",SUMPRODUCT((Sheet1!$A$1:$A$5>B2)*(Sheet1!$A$1:$A$5<C2)*(INDEX(Sheet1!$B$1:$IV$5,,ROW(A1)))))
という数式を入れ列ごとの小計を表示させておきます。
このD2セルを下にオートフィルでコピーします。
最後に全てを合計するようにしてみました。

尚、数式は当方使用のExcel2003の最終列IV列までの範囲指定をしています。

この程度の案しか浮かびませんが
他に良い方法があればごめんなさいね。m(__)m

お礼

回答ありがとうございます。
別シートでの計算の方が私も良いと思いました。
ご丁寧に画像までつけて頂き、助かりました。

回答No.3

MackyNo1

以下のような数式が簡単かもしれません。

=SUMPRODUCT((A1:A5>15)*(A1:A5<35)*B1:B5+(A1:A5>25)*(A1:A5<45)*C1:C5)

お礼

説明しやすいようにＢ列とＣ列だけにしましたが、本来もっと多くの列があるという説明を忘れていました。
SUMPRODUCTは知りませんでした。
200列近くはあるので回答頂いた数式をそのまま使うと非常に長くなってしまうのですが、
参考にさせていただきます。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

=SUMIF(A1:A5,">15",B1:B5)-SUMIF(A1:A5,">=35",B1:B5)
+SUMIF(A1:A5,">25",C1:C5)-SUMIF(A1:A5,">=45",C1:C5)
でいいような？

補足

説明不足でした。条件式には数式を使いたいと思っています。
質問どおりだと実行できるのですが、条件式に数式を使用できるでしょうか？
>15　などの数字の部分を数式に変更したらうまくいきませんでした。