条件を満たす個数のカウントについての質問です。

質問

条件を満たす個数のカウントについての質問です。

ID 　　　A　　　 B 　C 　x1 　x2 　xx 　Ｄ 　Ｅ
score 1　　　　80 2 　0 　 　30 　2 　3 　4
score 2 　　　5 　　　　0 　6 　5 　3 　80 　0
score 3 　　　84 5 　50 　100 　50 　20 　5
score 4 　　　3 　　　　0 　5 　6 　8 　0

上記のようにＩＤ　Ａ，Ｂ，Ｃ，，，に対して、各score(1から1000）が記載されています。ここで、score1,2,3,4,,,の中で、少なくとも1つのscoreで80以上100以下の値をとっている者は何人いるか？を計算したいです。
score 1 で80以上100以下の人数は、
=SUMPRODUCT((2:2>=80)*(2:2<=100))でだせます。（COUTIFSはexcelのバージョンが古くて使えません）
上記の計算を考えた場合、
Ａがscore1-1000で80以上100以下を取っている回数は
=SUMPRODUCT((Ｂ2:1000>=80)*(Ｂ2:1000<=100))で、少なくとも1回というのを、
=IF(SUMPRODUCT((Ｂ2:1000>=80)*(Ｂ2:1000<=100))>0,1,0))として1としました。
これを各Ｂ，Ｃ、、、に対して行い（値は0か1になりますよね）、その和を求めれば、良いかと考えました。
しかし、作業が煩雑になるので、できれば、一つの式でできるような方法を教えて頂けないでしょうか？
また、上記の表で、80以上100以下のscoreはどれ位あるかは、
=SUMPRODUCT((A2:HH1000>=80)*(A2:HH1000<=100))として計算できますが、
これから、ｘ1、ｘ2、xxなどxを含ん=SUMPRODUCT((B14:I17>=80)*(B14:I17<=100))だ物を除きたい場合はどうしたら良いでしょうか？sumproductではワイルドカードが使えないため、各ｘ1、ｘ2、xx、、に対して、下記の様に計算して
=SUMPRODUCT((A2:HH1000>=80)*(A2:HH1000<=100))*(A2:HH1000=”ｘ1”))*
コレを上の値から引いていくと言うのはあまりに大変です。
何か良い方法はあるのでしょうか？
以上、2項目、質問させて頂きます。

回答No.1

soixante

まずシートの形式がいまいち分かりませんので確認を含めて書きます。

１．A2セルから下にA1001セルまで、score1 ～　score1000 と書いてある。
２．B1セルから右にHH1セルまで、ID（人の名前？215人分？）が書いてある。
３．B2セルからHH1001　まで、点数が書いてある。点数は0～100？　「<=100」などとしてますから、100以上も存在するんですかね？

上記のイメージで考えました。
私ならめんどくさいので、作業用に別シートにしてしまいます。

【1】現在のシートをコピーし、Sheet2　とする。元シート名はSheet1

【2】Sheet2　の　B2セルに、

　　　　IF(OR(Sheet1!B2<80,Sheet1!B2>100),0,1)

　と入れて、Sheet2のB2:HH1001　の範囲まで引っ張る。
　※Sheet1 の値が、80未満 or 100超の場合は0、80以上100以下の場合は1　を返すようにしました。

【3】各人ごとに、「少なくとも１つのスコアでは80以上100以下を取ったことがある」かどうかを判定する為に、
　　「全てのスコアで80以上100以下を取ったことがない」と考えました。

Sheet2　の　B1002セルに、=SUM(B2:B1001) とすれば、Aさんが80以上100以下を取った回数が出ますよね。
つまり、1002行目は、各人ごとに全問の中で、80以上100以下を取った回数　となります。
ここがゼロの人は、「全てのスコアで80以上100以下を取ったことがない」ことになりませんか。

ゼロの個数を数えてみます。　HI1002セルに　=COUNTIF($B$1002:$HH$1002,0)

２つ目のご質問については、私が正しく認識しているか分かりませんが、ID（人名）の中で、ある文字（例えばx）を含む人は除く、ということでしょうか。

もしそうなら、やっぱり作業列を使っちゃいますかね。
1003行目に判定列を。

B1003セルに

=IF(ISERROR(FIND("x",B1)),1,0)

として、H1003セルまで引っ張る。
（IDに、"x" を含まなければ、1、含んでれば0を返しています。）

そのうえで、
B1004セルに、=B1002*B1003 として、HH1004まで引っ張る。

1004行目の合計数値が、「80以上1000以下のスコア」の個数になると思います。
以上です。

ご意向にそぐっていればいいですが、私の認識に誤解があるようでしたらお知らせください。

お礼

ご回答ありがとうございます。
そうですね。いっきに一つの式で考えるより、別の項目を作って、範囲を調べていったほうが、簡単ですね。
これで、やっていきます。
ありがとうございました。