ＳＵＭ関数を教えてください

質問

エクセル画面です

　　　　　Ａ　　　　　Ｂ　　　　　Ｃ
１　　　　氏名　　点数
２　　　　Ｈ氏　　　10
３　　　　Ａ氏　　　20
４　　　　Ｓ氏　　　30
５　　　　Ｔ氏　　　40
６　　　　Ｚ氏　　　50
７　　　　D氏　　　60
８　　　　合計　　　　　

Ｓ氏を基準としてＳ氏が（Ａ４）の位置にある場合の合計を出すときの計算式は
　　Ｂ８＝ＳＵＭ（Ｂ４：Ｂ７）　　となります
Ｓ氏が（Ａ５）の位置に来た場合
　　Ｂ８＝ＳＵＭ（Ｂ５：Ｂ７）　　となります

Ｓ氏のセルの位置が上下に移動するので
ＳＵＭ関数で範囲を自働的に変動させて計算させる方法を
教えてください
よろしくお願いいたします。

回答No.3

tom04 · ベストアンサー

こんばんは！
以前にも似たような感じの質問があったと思います。
その場合は検索データ（S）より上側の合計だったような気がします。

今回は検索データが出現した次の行からの合計ということみたいなので・・・
一例です。
お示しの表だけだとして、

B8セルに
=SUM(INDIRECT("B"&MATCH("S",A1:A7,0)+1):B7)
としてみてはどうでしょうか？

尚、「S」が複数出現する場合や、データ量が変わる場合は他の方法を考える必要があると思います。
m(__)m

お礼

早々のお返事ありがとうございます
感謝いたします

回答No.5

imogasi

>Ｓ氏を基準としてＳ氏が（Ａ４）の位置にある場合の合計を出すときの計算式は
　　Ｂ８＝ＳＵＭ（Ｂ４：Ｂ７）　　となります
そもそもどういう仕掛けの場合、こういう必要が起こり、またその結果が意味を持つことが可能なのか？
珍奇な感じだ。
質問するのだったら、それぐらい説明して質問すべきでしょう。
それ（S氏）より下の行を合計するなんて、その下の行の人はS氏の部下や同じ部署とかか？
それなら、部署コードを列に設けてSUMIF関数を使うのが正統な方法かな。

お礼

回答ありがとうございました
おかげさまで問題が解決いたしました
今後ともよろしくお願い致します

回答No.4

Cupper-2

まずこの場合、S氏がどのセルに入力されているかを調べることをします。
これは紙の上で計算を行う時も同じですよね。

この場合、「MATCH関数」 を使うと良いでしょう。
　=MATCH("S氏",A2:A7)
この場合、S氏の名前が入力された場所が上から数えて何番に目あるかが 1～6 と言う数値で返ってきます。
これで S氏 の名前が 検索した範囲のどこに入力されているのかを知ることができます。

ここまでできれば、あとはいろいろな方法を使って合計を求めることができます。
OFFSET関数、INDIRECT関数、などと SUM関数を組み合わせることで解決できますよ。

OFFSET関数
　B7セルを基準に上方向へ範囲を決めることで合計を求めることができます。
　=SUM(OFFSET(B7,0,0,-7+MATCH("S氏",A2:A7),1)

INDIRECT関数
　文字列で範囲を指定することができます。
　=SUM(INDIRECT("B"&1+MATCH("S氏",A2:A7)&":B7"))

など。
お奨めは OFFSET関数。基準からどこまでの合計を出せばいいのかと考えればOK。

※ OFFSET関数や INDIRECT関数の詳しい使い方は Excelのヘルプを参照してください。

お礼

回答ありがとうございました
おかげさまで問題が解決いたしました
今後ともよろしくお願い致します

回答No.2

hallo-2007

Index関数を使ってセルを指定することが出来ます。
=SUM(INDEX(B1:B7,MATCH("Ｓ氏",A:A,FALSE)):B7)
で大丈夫です。
Sum関数の最後のセルはB7ですが
始まりのセルを
INDEX(B1:B7,MATCH("Ｓ氏",A:A,FALSE))
で指定します。
空いているセルに
=INDEX(B1:B7,MATCH("Ｓ氏",A:A,FALSE))
と入れてみてください。
Ｓ氏のとなりの値が出るはずです。

お礼

回答ありがとうございました
おかげさまで問題が解決いたしました
今後ともよろしくお願い致します

回答No.1

keithin

=SUM(INDEX(B2:B7,MATCH("Ｓ氏",A2:A7,0)):B7)
とか，あとはOFFSET関数などを使う手もあります。
あれよく見たらＳ氏がリストに居ない!なんて状態の手当はしていませんが，必要なら工夫して下さい。

＃注意
＞Ｓ氏が上下に移動する

具体的に，一体どのように移動するのか
　・単なる計算結果として表示が入れ替わる
　・並べ替えて入れ替わる
　・行挿入や行削除によって上下する
　　etc,etc
に応じて，考え方も数式も変える必要があります。
教わった回答いろいろを実際に試してみて，ホントの使用条件でよく検討してから必要に応じて補足なりなんなりをしてみてください。

お礼

回答ありがとうございました
おかげさまで問題が解決いたしました
今後ともよろしくお願い致します