条件集計(複数条件)

条件集計で、SUMIF関数までは使える方も多いと思います。しかし、条件判断が複数列のAND条件になるとお手上げになってしまいます。これを解決するのが、SUMPRODUCT関数です。
SUMPRODUCT関数をヘルプで見ても、このようなことができるようには見えません。そこで諦めていた方が多いと思います。「配列数式」も同様ですね。
SUMPRODUCT関数はテーブル相互間の「積」の合計を計算してくれますが、この中で条件式を使うと「条件の成立は1」「成立しなければ0」としてかけ算されるため、結果としてAND条件(全ての条件式が「1」なら加算される)の合計になるのです。

実際にサンプルを作って見ました。
SUMPRODUCT関数のサンプル
(画像をクリックすると、このサンプルがダウンロードできます)
「B表」の所に「項目」と「氏名」のAND条件で集計をかけている簡単なサンプルです。「項目」と「氏名」は全て2文字なので、一旦外部の関係ない場所に「項目」と「氏名」を繋げて1項目に納めれば「SUMIF関数」でも算出できるのですが、ここでは余分な領域は全く使わずに同じことを実現します。しかも、参照相手を「B表」の項目見出しの名称としているので「相対参照」と「絶対参照」の行と列の使い方をうまく行なえば、今表示されているF7セルに正しい計算式をあてはめるだけでその他の5つのセルには計算式をそのままコピーさせるだけで実用できるようになります。
R1C1参照形式の場合は

 =SUMPRODUCT((R3C1:R8C1=R6C)*(R3C2:R8C2=RC5)*(R3C3:R8C3))
となります。

このサンプルのSUMPRODUCT関数のカッコ内は、かけ算の「*」で区切られていますが、区切られた各要素の配列個数が合っていなければならないのは「当たり前」です。
ここでは、
の3つの配列個数はどれも6つのなっています。

この条件の列をさらに増やして利用することもできます。関数要素は1つの計算式で32個まで使えます。


Excel2007以降(*.xlsx、*.xlsm)ではSUMIFS関数が追加されました。
SUMIFS関数」はこのような複数列でのAND条件で集計を行なう関数です。
SUMIFS関数のサンプル
配列数式やSUMPRODUCT関数は複合条件のAND条件集計では少し理解しにくい(かけ算とかだからでしょうか)ようだったのですが、 こちらは最初から「複合条件集計関数」ですから理解しやすいかも知れません。
このように処理結果は同じです。

カッコ内の引数は第1引数が合計範囲で、第2引数以降は偶数位置が条件範囲、奇数位置がその条件になります。
R1C1参照形式の場合は

 =SUMIFS(R3C3:R8C3,R3C1:R8C1,R6C,R3C2:R8C2,RC5)
となります。

[引数説明(A1形式はF7セル時点)]
A1形式R1C1形式機能
@  $C$3:$C$8  R3C3:R8C3  集計範囲(サンプル画像の「点数」列)COUNTFS関数では使用しない
A  $A$3:$A$8  R3C1:R8C1  条件判定範囲@(サンプル画像の「項目」列)
B  F$6  R6C  条件@(サンプル画像のセルだと「数学」) ※このサンプルでは他セルにコピーするため行のみ絶対参照
C  $B$3:$B$8  R3C2:R8C2  条件判定範囲A(サンプル画像の「名前」列)
D  $E7  RC5  条件A(サンプル画像のセルだと「奥居」) ※このサンプルでは他セルにコピーするため列のみ絶対参照

条件は、このサンプルのように単に値を指定すると完全一致として処理されますが、SUMIF関数と同様で文字列として「">1"」「"<>0"」というような指定も可能で、 条件の件数は最大127種類まで可能なようです。(限度は確認していません)

SUMIFS関数」の他、複数条件個数集計の「COUNTFS関数」、条件平均算出の「AVERAGEIF関数」、複数条件平均算出の「AVERAGEIFS関数」もExcel2007以降(*.xlsx、*.xlsm)で追加されています。
上記画像をクリックしてダウンロードできるサンプルには、このシートサンプルのままですが、「COUNTFS関数」と「AVERAGEIFS関数」のサンプルも含めてあります。 「COUNTFS関数」の方は集計しないので上の画像と比べると先頭の引数がない状態ですが、「AVERAGEIFS関数」の方は平均を集計するので引数は全く同じです。 但し、0件の時に「#DIV0」エラーになってしまうので「IFERROR関数」を重ねて使用しています。