（Excel関数）複数条件の合計を作成する方法

質問

（Excel関数）複数条件の合計を作成する方法

こんにちは。

Excelの元データが下記の通りあります。（下記空欄は本来数字が入力されている）

　　　　　　　Apr　May　Jun　Jul　Aug
A Sales amount
A COGS
A GP
A Expense
A Profit
B Sales amount
B COGS
B GP
B Expense
B Profit
C Sales amount
C COGS
C GP
C Expense
C Profit
A Sales amount
A COGS
A GP
A Expense
A Profit

このチャートを下記のように置き換えたいと思います。ただし、条件がやや複雑です。

1.月の部分はプルダウンで設定したいということ
（プルダウンを変えると下記の表示内容も変わる）
2.例えばAについてのSales amount....の情報は表中複数あるため「合計値」を求めたい
ということ。
Apr
　　　　　　　　　A　B　C
Sales amount
COGS
GP
Expense
Profit

AについてのSales amount....の情報が1回のみならばvlookupとmatch関数の組み合わせで
出来たのですが、表を良く見ると何度もAについての同じ情報が出ているためSUMを取らなくては
いけないのがネックです。SUMPRODUCT関数を使ったのですが、月を変えた場合に合計させる列を
どうやって変化させればいいのかわからずつまづいてしまいました。

良い方法をご教示頂けると助かります。

回答No.3

kagakusuki

今仮に、Sheet1のB1～M1の範囲にApr～Marと月の名前が並び、2行目以下99行目にまで、A列には「ASales amount」や「CProfit」等の「アルファベットの名称＋金額の種類」形式の名称が並び、B列～M列には数値がならんでいる一方、別のシートには、A1に月の名前が入力されていて、A2セルには「Sales amount」、A3セルには「COGS」、A4セルには「GP」、A5セルには「Expense」、A6セルには「Profit」と入力されていて、B1セルから右に向かって、「A」、「B」、「C」・・・・・等の名前が（順不同に）並んでいて、B列及びそれより右側にある列の2行目から6行目にかけて、各項目ごとの合計値を表示させるものとします。

　そのための方法として、SUMPRODUCT関数を使う方法と、SUMIF関数を使う方法の、２通りの方法を紹介させて頂きますが、SUMIF関数を使う方法の方がスマートだと思います。

　まず、合計値を表示させるシートのB2セルに、以下の２つの数式の内のどちらかを入力して下さい。

=SUMPRODUCT(OFFSET(Sheet1!$A$2:$A$99,,MATCH($A$1,Sheet1!$B$1:$M$1,0))*(Sheet1!$A$2:$A$99=B$1&$A2))

=SUMIF(Sheet1!$A$2:$A$99,"="&B$1&$A2,OFFSET(Sheet1!$A$2:$A$99,,MATCH($A$1,Sheet1!$B$1:$M$1,0)))

　次に、B2セルをコピーして、各項目ごとの合計値を表示させるセルが存在している範囲に貼り付けて下さい。
　これで、各項目ごとの合計値が表示される様になる事と思います。

回答No.2

mshr1962

＞すいません、数式の通りに入力したのですが、なぜか0が返されてしまいます。。。
No.1です。こちらで試す限り正常に計算しますので、下記を確認してください。

・シート名が違ってないか？
・セルの範囲がずれていないか？
・計算方法が手動になっていないか？

一応こちらで試したエクセルの表を貼り付けます。

回答No.1

mshr1962

シート1
A列に A,B,C
B列にSales amount,COGS,GP,Expense,Profit
C列以降が月データで1行目にApr,May,Jun,Jul,Aug
として

シート2
A1="Apr"
A3="Sales amount"
B2="A",C2="B",D2="C"
B3=SUMPRODUCT((シート1!$A$2:$A$21=B$2)*(シート1!$B$2:$B$21=$A3)*OFFSET(シート1!$B$2,0,MATCH($A$1,シート1!$C$1:$G$1,0),20,1))
B3の式をコピーして必要な範囲へ貼り付けしてください。

お礼

すいません、数式の通りに入力したのですが、なぜか0が返されてしまいます。。。