SUMPRODUCT関数では、「セル範囲＝＊回答番号＊」のようなワイルドカードを使った条件は
指定できないので、FIND関数を利用した条件式で複数回答の集計を行う。
複数回答の集計を行うには、「NOT(ISERROR(FIND(H3,E3:E10)))」のような条件式を設定する。
要となるのがFIND関数で、H3セルに入力されている「1」の回答番号が、E3〜E10の各文字列に
含まれているのかを検索する。この際、含まれていればその文字位置を示す数が返されるが、見つ
からない場合はエラーが返されてしまう。そこで、「NOT(ISERROR(FIND)」の構造にして、
「FIND関数がエラーにならない」＝「回答番号が含まれる」と言う条件として指定する。
年代部分は、「D3:D10＞=G4)＊(D3:D10＜G4+10))」となり、下限値と上限値を決めている。
セルH6に、
「=SUMPRODUCT(NOT(ISERROR(FIND(H$3,$E$3:$E$10)))＊($D$3:$D$10＞=$G6)＊($D$3:
　$D$10＜$G6+10))」と入力すれば、「30代」の集計ができる。

上記は、アンケートの「趣向」が複数回答ある場合の「年代」と「趣向」の集計である。
セルH4に、
「COUNTIFS($E$3:$E$10,"＊"&H$3&"＊",$D$3:$D$10,"＞="&$G4,$D$3:$D$10,"＜"&$G4+10)」
と入力すると、「0」と表示された。
これは、E列の「趣向」の「回答番号」から「10代」の「趣向」1番を集計したものである。
「10代」の男性は、「2」と「回答しているので「1番」は無いので「0」になる。
複数回答のデータ入力には、回答番号をカンマで区切って入力する方法がよく採られている。
区切り記号は、何でも構わないが、選択肢が10以上ある場合は、「1」は「01」と桁数を統一する
必要がある。又、表中の8行目の「趣向」の様に、「2」だけの場合でも、「2,」と区切りカンマが
必要である。
こうした前提で、複数回答の集計をするには、「回答番号が含まれるか」と言う条件を指定する。
COUNTIFS関数の条件には、ワイルドカードが使えるので、「＊回答番号＊」の様に前後に「＊」を
付けることで、回答番号を含む件数の集計ができる。

上図のセルG3に、
「=SUMPRODUCT(($C$3:$C$10=G$2)＊($D$3:$D$10＞=$F3)＊($D$3:$D$10＜$F3+10))」と
入力すると、「1」と表示された。これは、D列の中から「男」の「10代」の人数を数えたものである。
SUMPRODUCT関数を使えば、性別と年代別のクロス集計が行える。
この場合は、「性別」と「年代」の「下限値」と「上限値」の3つの条件を指定する。




H3セルにコピーすると、
「=SUMPRODUCT(($C$3:$C$10=H$2)＊($D$3:$D$10＞=$F3)＊($D$3:$D$10＜$F3+10))」と
なり、「女」の「10代」が集計できる。

上図のセルG3に、
「=COUNTIFS($C$3:$C$10,G$2,$D$3:$D$10,"＞="&F3,$D$3:$D$10,"＜"&$F3+10)」と
入力すると、「1」と表示された。
これは、左の表から「10代」の「男」を集計した人数である。
基本的には、年代別の「下限値」と「上限値」の2条件に加えて、「性別」の3つの条件を指定する。
ここでは、COUNTIFS関数を利用して、「性別」の条件を追加する。
「$C$3:$C$10,G$2」として、「性別」分の条件を加えている。
右と下にコピーしてもよいように、必要なセルは絶対参照にしている。




H3セルにコピーすると、
「=COUNTIFS($C$3:$C$10,H$2,$D$3:$D$10,"＞="&F3,$D$3:$D$10,"＜"&$F3+10)」と
　なる。

上図のセルG3に、「=SUMPRODUCT((D$3:D$10＞=F3)＊(D$3:D$10＜F3+10))」と
入力すると、「1」と表示された。
これは、D列の中から10歳以上20歳未満の人数を集計したものである。
G3セルをG7までコピーしても集計された値は正常である。
SUMPRODUCT関数の、二つの条件の件数を求める公式に当てはめたものである。
年代集計するときは、年代の「下限値」と「上限値」を条件に指定する。
図のF3からF7には、「10」「20」「30」「40」「50」と数値が入力されていて、表示形式
で「10代」と見せているので、これを使って条件を指定する。
下限値の条件は、「(D$3:D$10＞=F3)」として「年齢範囲＞＝10」のようになり、
上限値の条件は、「(D$3:D$10＜F3+10)」として、「年齢範囲＜20」のような条件になる。
これらを同時に満たすAND関数として指定するので、これらの条件式を「＊」で掛け算する
事によって求めることができる。
「SUMPRODUCT((D$3:D$10＞=F3)＊」・・「年齢＞＝10」のような条件
「(D$3:D$10＜F3+10))」・・・・・・・・「年齢＜20」のような条件

上図は、D列の中から年代別に集計して、G列に表示してものである。
セルG3に、「=COUNTIF(D3:D10,"＜"&F3+10)」と記入すれば、「1」と10代の
人数が表示された。これは、D列から10歳以上20歳未満の人数を数えたものである。
条件が一つの場合なら、COUNTIF関数で集計をすることができる。



しかし、上図の様にセルG３の式と同じ式を、セルG4に入力すると集計が間違って表示
される。これは、「30歳未満」として、10代の人数を「1」と20代の人数「2」が合計
されて表示される。


そこで、SUM関数を使って、すでに求めている前の年代を合計して、それを差し引くように
したものが上図である。
セルG4に、「＝COUNTIF(D$3:D$10,"＜"&F4+10)−SUM(G$3:G3)」と入力すると
「2」と表示された。これは、20歳以上30歳未満の人数である。以下のセルにコピーしても
集計できる。ポイントは、「−SUM(G$3:G3)」と範囲の起点のG3にだけ「＄」マークを付
けて絶対参照にし、以下のセルにコピーする点にある。

FREQUENCY関数は、「頻度集計」や「区間集計」と呼ばれる集計が行える関数で、
連続した数値を階級ごとに区切った件数を求めることができる。


上図のセルI３に、「{=FREQUENCY(D3:D10,H3:H7)}」と数式が入っている。セルに「1」
と表示されている。これは、D列の年齢表を基に年代別に集計したものが、I列に表示された
ものである。
FREQUENCY関数を使うときには、まず階級の上限値を入力する。但し、上限値だけでは
分かりにくいので、下限値も入力して明確にしておくのが良いだろう。



では、関数の入力方法を説明しよう。
まず、セルI3からI７を選択して、「=FREQUENCY(D3:D10,H3:H7)」と入力する。
その状態で、[Ctrl]+[Shift]+[Enter]キーを同時に押す。すると、配列数式として入力
されて集計される。



尚、配列数式として入力すると、数式が「{ }」で囲まれる。

上図のセルＧ3に、「＝COUNTIFS(D$3:D$10,"＞="&F3,D$3:D$10,"＜"&F3+10)」と
入力すると「1」と表示された。これは、D列の中から、１０歳以上で２０歳以下の人数を
数えたものである。
COUNTIFS関数の条件で、セルの値を使って「"＞="F3」のようにして、「＞=10」と言う
条件と、「"＜"&F3+10」のようにして「＜20」と言う２つの条件を指定している。

「=COUNTIFS(D$3:D$10,」・・・年齢が入力されている範囲。
「"＞="&F3,」・・・・・・・・・「＞=10」となり、「10以上」と言う条件。
「D$3:D$10,」・・・・・・・・・「年齢が入力されている範囲。
「"＜"&F3+10」・・・・・・・・「＜20」となり、「20未満」と言う条件。



尚、F列の「年代」は、セルに数値だけ入力し、「ユーザー定義」で「10代」と表示している。

上記の図は、E列の空白セルを数えるものである。
H3セルに、「SUMPRODUCT((D$3:D$10=G3)＊ISBLANK(E$3:E$10))」と入力すれば、
「1」と表示された。これは、男女別にE列の空白セルを数えて表示したものである。
ISBLANK関数は、厳密に空白セルを判定するもので、「ISBLANK(E$3:E$10)」することで、
空白セルの件数を集計できる。



尚、セルＥ3には、「0:05:20」と入力してあり、「セルの書式設定」で「5分20秒」と表示
している。方法はセルを右クリックし、[セルの書式設定]を選択し、[表示形式]の[ユーザー定義]
で[種類]に、「m"分"ss"秒"」と記入すると、[サンプル]に「5分20秒」と表示されるので[OK]を
押す。セルにも「5分20秒」と表示される。

上記の図は、男女別の棄権人数を数えたものである。男女別条件は、元データの「性別」欄を
参照する条件式「$D$3:$D$10=G3」とすれば、元データのD3〜D10セルと、集計表のG3セル
「="男"」と比較ができる。そして棄権人数は、元データの「記録」欄に、「途中棄権」と
文字列で入力されているセルと言うことになる。そこで、文字データかどうか判断するのが、
ISTEXT関数で「ISTEXT(E$3:E$10)」とすることで、文字データの件数を集計できる。
セルH3に、「=SUMPRODUCT((D$3:D$10=G3)＊ISTEXT(E$3:E$10))」と入力すれば、
「1」と表示された。
「=SUMPRODUCT((D$3:D$10=G3)＊」、D3〜D10セルの性別を判断する条件式。
「ISTEXT(E$3:E$10))」、E3〜E10セルが文字列か判断する条件式。

上記のセルH3に、「＝SUMPRODUCT((D$3:D$10=G3)＊ISNUMBER(E$3:E$10))」と入力
すると、「3」と表示された。これは、E列に「数値」が記入されていないと数えないもので、
「女」の9行目の「途中棄権」は数値でないので数えない。
「ISNUMBER(E$3:E$10)」と記入することで、数値データの数を数えるのである。
尚、「記録」のセルには、時間を「0:05:20」と数値で記入されている。

上記は、男女別の出席人員を数えるものである。E列に「出席」と書かれていないと数えない。
セルH3に、「=SUMPRODUCT((D$3:D$10=G3)＊(E3:E10＜＞""))」と入力すると、「3」と
表示された。これは、「男」4人の内、1人が出席していないから3人と数えられた。
「＜＞""」これの意味は、「空白でないと」数えると言うことになる。だから空白は数えない。

今度は、3列ごとに入力されているデータを集計してみよう。
L4セルに、「=SUMPRODUCT((MOD(COLUMN($C4:$K4),3)=0)＊1,$C4:$K4)」と入力すると、
「840」と表示された。これは、C列、F列、I列の「食品」の数値合計である。
「食品」が始まるC列は、左から「３」番目となるので、COLUMN関数で得られる列番号は、「3」と
なる。よって、L４セルに入力する数式の条件部分は、「MOD(COLUMN(C4:K4),3)=0」となる。
3÷3＝0である。



上記のM4セルに、「=SUMPRODUCT((MOD(COLUMN($C4:$K4),3)=1)＊1,$C4:$K4)」と入力
すると、「790」と表示された。これは、D列、G列、J列の「菓子」の数値合計である。
「菓子」が始まるD列は、左から「４」番目となるので、「３」で割った余りは、「１」となるため、
M4セルに入力する数式の条件部分は、「MOD(COLUMN(C4:K4),3)=1」となる。
4÷3＝余り1である。



上記のN４セルに、「SUMPRODUCT((MOD(COLUMN($C4:$K4),3)=2)＊1,$C4:$k4)」と入力すると
「440」と表示された。これは、E列、H列、K列の「雑貨」の数値合計である。
「雑貨」が始まるE列は、左から「5」番目となるので、「3」で割った余りは、「2」となる為、
N4セルに入力する数式の条件部分は、「MOD(COLUMN(C4:K4),3)=2」となる。



この考え方で、4列おき以上の列数が増えた場合の、計算式がどうなるのかを見てみよう。
上図は、4列おきの計算式で、「SUMPRODUCT((MOD(COLUMN($C4:$N4),4)=3)＊1,$C4:$N4)」
となり、MOD関数の部分を見ると、「MOD(COLUMN($C4:$N4),4)=3)」となり、C列は3列目で
4列おきの値を合計するのだから、「３」を「４」で割ることになり、割り切ることができないので
余りが「３」になる。だから「(MOD(COLUMN($C4:$N4),4)=3)」となる。



「菓子」の場合は、始まり列が「４」列目なので、「４」は「４」で割り切れるので、余りは「0」と
なり、「(MOD(COLUMN($C4:$N4),4)=0)」となる。
このように、「雑貨」の場合は、始まり列が「5」なので、「5」割る「４」は、余りが「1」となり
「本」は、始まり列が「６」なので、「６」割る「4」は余り「2」となり、
「(MOD(COLUMN($C4:$N4),4)=1)」、「(MOD(COLUMN($C4:$N4),4)=2)」となる。
5列おきの集計では、「食品」の始まりは3列目だから、「3」割る「5」は割れないので、余り「3」に
なり、「４」割る「５」は割れないので、余り「４」となる。
何列、何行おきの集計でも、この考え方で計算式ができる。

2行ごとに入力されている場合と同じように、3行や4行毎に繰り返し入力されている場合でも
同様に計算ができる。
上図の例は、3行ごとに入力されているので、3で割った「余り」によって条件を分けて集計
することができる。
セルD13に、「=SUMPRODUCT((MOD(ROW(D$4:D$12),3)=1)＊1,D$4:D$12)」と入力
すると「830」と表示された。
これは、「食品」は4行目から入力されているので、「3」で割った余りは「1」になる。
これにより、「MOD(ROW(D$4:D$12),3)=1」の条件で求められる。
4行目と7行目と10行目を合計したものである。この3行番は3で割ると1余るからである。



セルD14に、「=SUMPRODUCT((MOD(ROW(D$4:D$12),3)=2)＊1,D$4:D$12)」と入力
すると「900」と表示された。
これは、「菓子」は5行目から入力されているので、「3」で割った余りは「2」になる。
5行目と8行目と11行目を合計したものである。この3行番は３で割ると２余るからである。



上図のセルD15に、「=SUMPRODUCT((MOD(ROW(D$4:D$12),3)=0)＊1,D$4:D$12)」と
入力すると「380」と表示された。
これは、「雑貨」は6行目から入力されているので、「3」で割った余りは「0」になる。
「=SUMPRODUCT((MOD(ROW(D4:D12),3)=0)＊1,D4:D12)」は、D4からD12行の中で
行番号を3で割った余りが0の行を選択し、そのD列の数値を合計すると言うことになる。
6行目と9行目と12行目の3行番号は、３で割ると余りは0だから合計される。

1列おきに集計したい場合は、COLUMN関数で列番号を取得する。規定値のセル番地の表記では、
「A」列のようになっているが、COLUMN関数は、「A」列を「1」とした列番号が取得できるので、
行と同じように「MOD(COLUMN(セル),2)」とした余りで、偶数列と奇数列を分けて集計できる。
奇数列は、「=SUMPRODUCT((MOD(COLUMN(C4:H4),2)=1)＊1,C4:H4)」となり、
偶数列は、「=SUMPRODUCT((MOD(COLUMN(D4:H4),2)=0)＊1,D4:H4)」となる。

上記の表内のデータを、1行おきに合計する集計は、行番号の偶数行か奇数行で判別して集計する
考え方が成り立つ。
表のセルD9に、「=SUMPRODUCT((MOD(ROW(D3:D8),2)=1)＊1,D3:D8)」と入力すると、
「830」と表示された。
これは、行番号3行目から8行目までの内の、奇数行セルの値を合計したものである。


上図は、セルD10に、「=SUMPRODUCT((MOD(ROW(D3:D8),2)=0)＊1,D3:D8」と入力した
もので、「900」と表示されている。これは、行番号の偶数行だけを合計したものである。
奇数行か偶数行かは、MOD関数で「MOD(ROW(セル),2)」として、セル番号を2で割り、その余り
が0の時は偶数行、1の時は、奇数行となる。
MOD関数は、引数に「MOD(数値,除数)」を指定すると、「数値÷除数」の余りが表示される。
割り切れた場合は「0」が表示される。除数に「2」を指定し、余りが「1」なら奇数、余りが
「0」なら偶数と判断できる。

上記の表は、売上データから「平日」と「土日」を分けて売上数量を集計したものである。
セルG3に、「SUMPRODUCT((WEEKDAY($B$3:$B$9,2)＜=5)＊1,$D$3:$D$9)」と入力
すると、「430」とB列の中から「土日」を除いた「平日」の売上数量の合計が表示された。


上図のG4セルに、「SUMPRODUCT((WEEKDAY($B$3:$B$9,2)＞=6)＊1,$D$3:$D$9)」と
入力すると、B列の中から「土日」の売上数量の合計が表示された。
WEEKDAY関数は、曜日に対する番号を返す関数だが、第2引数に「2」を指定すると、
「月曜日〜日曜日」に対して「1〜7」の曜日番号を返す。



「WEEKDAY($B$3:$B$9, 」と入力すると[種類]が表示される、第2引数の「2」は赤線枠の部分
である。「月曜日〜日曜日」に対して「1〜7」の番号が付けられている。
第2引数に「2」を指定して、「(WEEKDAY($B$3:$B$9,2)＜=5)」のように、「＜=5」と書くと
「1〜7」の6以下を指定しているので、「7=日曜日」、「6=土曜日」なので「平日」を指定した
ことになり、「(WEEKDAY($B$3:$B$9,2)＞=6)」のように、「＞=6」と書くと「土日」を指定
したことになる。ここでも条件が一つの場合の公式だから「1」を掛けるのである。



尚、B3セルの内容は、「2008/4/1」と入力されていて、[セルの書式設定」で「yyyy/m/d(aaa)」
と[種類]に書いてある。

上図は、B列の日付データを期間内で集計したものである。
「日付」は、「$B$3:$B$9＞=G3」の様に比較演算子を利用することで、B3〜B9セルの
日付以降と条件にできる。これらを組み合わせることにより、指定期間内の条件で集計できる。
上記のように、セルJ3に、
「=SUMPRODUCT(($B$3:$B$9＞=G3)＊($B$3:$B$9＜=I3),$E$3:$E$9)」と入力すると
「220」と表示された。
J3セルの数式は、

となっている。
日付の実体は数値なので、日付の条件に比較演算子を使って「日付=基準日」や「日付＞=基準日」
の様に指定することができる。だから、実データは、「年／月／日」のように必ず「年」から
指定する必要がある。

上図は、売上データの4月分を4周分に分割して、週ごとの売上数量を集計表示したものである。
セルK3に、「=SUMPRODUCT(($D$3:$D$9=J3)＊1,$H$3:$H$9)」と入力すると、「150」
と表示された。これは、D列の月のうちの何週目かを見て、J３セルの「第一週」分の売上数量
を合計したものである。週数が表示されていれば、条件が一つの場合の公式で集計できる。



上図の様に、C3セルに「=WEEKDAY(B3)」と入力すると曜日が表示できる。



但し、上図のC11セルの様に、「=WEEKDAY(B11)」と入力しただけでは、「3」と表示される。



そこで、[セルの書式設定]で[ユーザー定義]の[種類]に「aaa」と記入すると、[サンプル]の
様に「火」と表示される。



なお、「火曜日」と表示したい場合は、「aaa"曜日"」と入力すればよい。
尚、式では「=SUMPRODUCT(($D$3:$D$9=J3)」のように、「第何週」と言う数値で集計して
いるので、下図のように、セルD3に「WEEKNUM(B3)−WEEKNUM("2008/4/1")+1」と入力
することにより、その日の月の週数を表示させることができる。式では、その数値を集計に使用
している。
WEEKNUM関数は、その年の最初から数えたもので、「日曜日」を始まりとするか、「月曜日」を
始まりとするかを指定できて、引数を省略すると「日曜日」始まりの週数を返す。
その週数から、該当月の1日の週数を引いて「1」を足すと、その月内の週数を求められる。
但し、そのままでは、「0」から数え始めることになってしまうので、「1」を足して調整する。
WEEKNUM関数の引数は、「=WEEKNUM(指定した日付,週の基準)」と指定する。
引数の「週の基準」は、「1」と指定すると、週の開始日が日曜日になり、「2」又は省略すると、
開始日が月曜日になる。





尚、J列の「第一週」の表示方法は、上図の様に、[セルの書式設定]の[ユーザー設定]で[種類]に
「"第"0"週"」と入力すれば[サンプル]に「第一週」と記入され、[OK]を押せば設定が完了する。

上図は、「支店」と「月」と「商品」の三つの条件で集計したものである。
セルI5に、
「=SUMPRODUCT(($C$3:$C$16=$I$2)＊($E$3:$E$16=$H5)＊(MONTH($B$3:$B$16)=I$4),
　$F$3:$F$16)」と入力すると、「100」と表示された。
これは、大阪支店の4月の「ブルゾン」の売上を集計したものである。
これの、I２セルの「支店名」を変えれば、支店ごとのクロス集計が表示できる。
支店名の「I2」セルは、数式をコピーする際も移動させないので、「$I$2」と行と列の両方を
絶対参照にしている。一方で商品名の「H5」セルは、列だけを固定して、「$H5」とし、月数の
「I4」セルは行だけ固定して、「I$4」としている。
I2セルは、支店名を変更するので、「データの入力規則」で「リスト」を指定して、支店名を
選択できるようにすると、目的のクロス集計表をすぐに表示できる。



「データの入力規則」の使用方法は、[データ]から[データの入力規則]を選択する。



[入力値の種類]に[リスト]を選択する。


表のC3からC5を選択すると、テキストBOXに記入されるので、「×」を押す。

戻ると、[元の値]にデータが入力されている。[OK]を押す。



表のI2セルの「▼」を押せば、一覧が表示されるので「支店名」を選択すれば表の
表示も切り替わる。

上図は、月と商品名をクロス集計したものである。クロス集計の基本形は一方の条件に月数を
指定すればよい。上記のようにセルI3に、
「=SUMPRODUCT(($E$3:$E$9=$H3)＊(MONTH($B$3:$B$9)=$I$2),$F$3:$F$9)」と入力
すると「50」と表示された。

I3セルの数式は、

となっている。

上図のJ3セルに、「=SUMPRODUCT(($C$3:$C$9=I3)＊1,$G$3:$G$9)」と入力すると、「50」と
表示された。これは、C列の中から4月分の売上数量を集計したものである。J5セルまで、コピーすると
月ごとの売上数量が集計される。


日付データから月を取り出すために、MONTH関数をC列のセルに使用している。
あとは、条件が一つの場合の公式を使用している。



上図の様に、セルI3に、「=SUMPRODUCT((MONTH($B$3:$B$9)=H3)＊1,$F$3:$F$9)」と
入力すると、「50」と表示された。この場合、月を別に取り出さないで同時に処理をしている。



MONTH関数を使えば日付から月数だけを取り出せるので、「MONTH($B$3:$B$9)」として、
B3〜B9までの月数を取り出し、これを集計表のH列セルと比較する条件式にする。
あとは条件が一つの場合の公式をあてはめて、「＊1」を指定する。尚、I列の入力は、セルI３を
クリックするとフィルハンドルが出るので、ダブルクリックするとI７まで入力される。


又、H列の月数が、「４月」と表示されているが、本当は数値だけが表記されていて、
ユーザー設定の表示形式で、月を表示している。
セルの書式設定は下記の様に行います。

上図のI3セルに、「=SUMPRODUCT(NOT(ISERROR(FIND(H3,$E$3:$E$9)))＊1,$F$3:$F$9)」と
入力すると、「190」と表示された。
これは、文字の一部だけが合致する条件を指定したい場合、例えばSUMIF関数を利用する場合などは、
「=SUMIF(E3:E9,"＊ブルゾン＊",F3:F9)」のように、条件値に「＊ブルゾン＊」とワイルドカードを
指定して行うことができる。しかし、「A1＝"ブルゾン＊"」のような比較はできないので、配列数式を
利用した条件に、文字列の一部を指定したこのような数式になる。



数式の要となるのは、「FIND(H3,E3:E9)」の部分だ、FIND関数でH3セルに入力されている
「ブルゾン」などをE列の各セルで検索して、もし見つければその位置を返すが、見つからない
場合はエラーとなる。これにより「NOT(ISERROR( ))」で「エラーが発生しない場合」＝「その
文字列が含まれる」のを条件にしている。あとは、条件が一つの場合の公式を応用している。

上図のH3セルに、「=SUMPRODUCT($E$3:$E$9=G3)＊1,$F$3:$F$9)」と入力すると、
「290」と表示された。これは、D列の商品の中から、商品名「ブルゾン」を合計したものである。
条件が一つの公式を活用すれば、商品ごとにデータを集計できる。条件が一つの場合の「＊1」を
忘れないようにしよう。

「OR条件」を指定する場合でも、異なる列を対象にして指定する場合は注意が必要になる。
異なる列を対象にする場合は、「どちらか満たす」を求めていても、「同時に満たす」場合も
ありうるので、「SIGN関数」を使用する。
上図のセルH17に、「=SUMPRODUCT(SIGN((C15:C21=H15)+(D15:D21=H16)),E15:E21)」
と入力すると、「290」と表示された。
これは、「衣類」か「ブルゾン」のどちらかが該当する「数量」の合計である。
この具体的な計算の仕組みを見てみよう。


計算条件の、「種別」か「商品」が合致するセルは、「TRUE」となる。いずれか一方でも満たせば
良いので、これらを足し算で計算する。
しかし、「種別」が「衣類」で「商品」が「ブルゾン」の場合には、どちらも「TRUE」になる。
このように、同時に満たす場合は、「2」となり、そのまま「数量」を掛け算してしまうと、2倍の
値になってしまう。そこで、「1以上」の場合は「1」とするようにしなければならない。
ここでは、その役目にSIGN関数を使用して、「1」か「0」に戻している。
SIGN関数は、正の値は「1」、ゼロは「0」、負の値は「−1」にできるので、これで変換してから
「数量」を掛け算している。

複数条件のいずれかを1つだけでも満たせばよい条件を「OR条件」と呼ぶ。
上記のセルH5に、「SUMPRODUCT((C3:C9=H3)+(C3:C9=H4),E3:E9)」と入力すると、
「260」と表示された。これは、C列の中の「衣類」と「履物」の数量を合計したものである。
「(条件範囲1＝条件値1)+(条件範囲2＝条件値2)」の様に各条件を「足し算」で指定するのが
基本である。


上記がその合計を求める仕組みである。条件に合致すれば、「TRUE」が返される。
どちらかが「TRUE」なら「1」となり、数量の値を掛け算すればその合計が求められる。

上図のセルI5に、「=SUMPRODUCT((C3:C9=I2)＊(D3:D9=I3)＊(E3:E9=I4))」と
入力すると「2」と表示された。これは、「東京」、「衣類」と「ブルゾン」の三つの
条件を指定して、該当するデータ件数を求めた結果である。
複数条件のデータ件数を求める式は、
「SUMPRODUCT((条件範囲1＝条件値1)＊(条件範囲2＝条件値2) {＊条件範囲3＝条件値3)...}
となる。ただ、「(条件範囲＝条件値)」の様にカッコで囲むのと、「＊」でつないでゆくのを
忘れないように指定する。尚この場合は、すべての条件を「同時に満たす」、「AND条件」と
なる。

上図のセルI3に、「=SUMPRODUCT((C3:C8=H3)＊(D3:D8=I2))」と入力すると「1」と
表示された。これは、「東京」と「衣類」と言う二つの条件で、該当するデータを求めた結果
である。「条件範囲1＝条件値1)＊(条件範囲2＝条件値2)」と各条件判断式をカッコで囲んで
掛け算を指定すれば良い。


各条件式の結果となる論理値を掛け算すると、すべて「TRUE」の時だけ「1」になり、
そのまま合計すればデータ件数が求められる。

上図のセルI3に、「=SUMPRODUCT((C3:C8=H3)＊1)」と入力すると「2」と表示された。
この計算の仕組みは、


条件式に該当する「C3:C8=H3」の部分が配列数式として、一つのセルずつ検証される。


この場合、「支店」を「東京」に指定しているので、「東京」が「TRUE」になりそれ
以外は「FALSE」になる。


条件が一つの場合は、「1」を掛けるのがポイントである。これにより「TRUE」を「1」、
「FALSE」を「0」に変換することができる。その結果データ件数が求められる。

上図は、SUMPRODUCT関数を使用して、「支店」、「種別」と「商品」の三つの条件を指定して
「合計数量」を求めたものである。
「=SUMPRODUCT((C3:C9=I2)＊(D3:D9)=I3)＊(E3:E9=I4),F3:F9)」となる。
複数条件の合計を求めるには、
「=SUMPRODUCT((条件範囲1=条件値1)＊(条件範囲2=条件値2)＊(条件範囲3=条件値3),集計範囲)
と「条件範囲=条件値」の掛け算を増やしてゆけば良い。
　第2引数には、「集計範囲」は指定する必要はない。
ただ、「(条件範囲=条件値)」の様にカッコで囲むのと、「＊」でつないでゆくのを忘れないように
指定する。尚、この場合は、すべての条件を「同時に満たす」、「AND条件」となる。

複数条件のデータ件数を求める公式は
「=SUMPRODUCT((条件範囲1=条件値1)＊(条件範囲2＝条件値2)＊(条件範囲3=条件値3)＊....)」
となる。

上図は、「支店」と「商品」の二つの条件で「数量」を集計したものである。
セルI3に、「=SUMPRODUCT((C3:C9=H3)＊(E3:E9=I2),F3:F9)」と入力すると、「250」と
表示された。これは、「東京」と「ハンガー」が同時に存在する行の数量を合計したものである。
SUMPRODUCT関数の第一引数にあたる部分に、「(条件範囲1=条件値1)＊(条件範囲2＝条件値2)」
の様に、各条件判断をカッコで囲んで、掛け算を指定するのがポイントである。
この場合カッコを忘れると、計算の順序が変わってしまうので注意が必要である。



計算の仕組みは上図のようになる。
「(条件範囲1＝条件値1)」の部分は、{FALSE,TRUE,FARSE,....}の様になり、
「(条件範囲2=条件値2)」の部分は、{FALSE,TRUE,FALSE,....}の様になる。
論理値を四則演算すると、「TRUE」は「1」、「FALSE」は「0」に換算されるので、
両方とも「TRUE」の場合のみ「1」となる。これをもう一方の引数「数量」と掛け算する
ので、条件に合致した合計だけが求められる。

SUMPRODUCT関数は、引数に指定した配列の要素同士を掛け合わせて、それらを合計した
値を求める事ができる。
上記の図のように、C6セルに、「=SUMPRODUCT(C3:C6,D3:D5)」と入力すると「33,900」
と表示された。これは、C列とD列の値を掛け算した合計である。
SUMPRODUCT関数は、配列関数になっていて、各引数に指定した配列(セル範囲）の各要素同士
を掛け算して、最終的にそれらの合計を求めるものである。



SUMPRODUCT関数は上図のような計算式で合計を求める。
内部的に、「単価」×「数量」を各行ごとに計算し、最終的にそれらの合計を求める。



上図は、SUMPRODUCT関数を使って、条件が成立するセルだけの合計を求めるものである。
セルI3に、「＝SUMPRODUCT((C3:C8=H3)＊1,F3:F8)」と入力すると、「160」と表示された。
これは、下記のような数式の仕組みになっている。



まず、SUMPRODUCT関数の第一引数になっている、「(C3:C8=H3)＊1」の部分に着目しよう。
このうちの「C3:C8=H3」は配列数式になるので、「C4=H4」「C5=H4」...の様になる。
この結果、{FALSE,TRUE,FALSE....}のような配列が得られる。
そして、これらに「1」を乗じると「TRUE」は「1」、「FALSE」は「0」として換算されるため、
{0,1,0,0,....}のような配列定数が得られる。
これが、SUMPRODUCT関数の一方の引数となり、もう一方の引数が「数量」の「F3:F8」となって
いるので、これらの各要素を掛け合わせた合計ができる。
第一引数の方で条件の合致する行が「1」になっていて、条件に合致しない行が「0」となっている
ため、結果的に条件に合致する行だけの「数量」の合計を求めることができる。

　
関数の実用事例と引数の解説
　条件集計に使用する関数の解説

実際に使っているシステムの内容で解説
　　EXCEL VBA 講座