一定のセル範囲で複数の条件に該当しないセルの有無を簡単に取得する方法

質問

A1:A20の範囲で、空白ではなく、A～Eや1～5のどれにも該当しないセルの有無を取得する場合、以下のような長ったらしい関数以外でもっと簡単な方法はないでしょうか？
なお、設定の検索値のA～Dや1～5は例であり、実際は別の規則性のない文字列および数値です。
宜しくお願いいたします。

=SUMPRODUCT((A1:A50<>"")*(A1:A50<>1)*(A1:A50<>2)*(A1:A50<>3)*(A1:A50<>4)*(A1:A50<>5)*(A1:A50<>"A")*(A1:A50<>"B")*(A1:A50<>"C")*(A1:A50<>"D")*(A1:A50<>"E"))=0

回答No.6

onlyrom · ベストアンサー

質問者のお好きな？SUMPRODUCTを使うなら

範囲Ａ１～Ａ２０　とした場合

=SUMPRODUCT((A1:A20={"","A","B","C","D","E",1,2,3,4,5})*1)=20

で、どうでしょうか。

見ればお分かりだと思いますが、質問者と逆で、
"","A","B","C","D","E",1,2,3,4,5
の数を数えています。

お礼

あははは！ (*´∇｀*)
別にSUMPRODUCTが好きなわけじゃないですけど、CSE数式よりはわかりやすいんですよねえ。なんせオツムがいたって単純にできてるものですから
　(^^;;

ありがとうございました。

回答No.5

mu2011

配列数式ですが如何でしょうか。
検索条件をE1:E9に設定しておきます。
=(COUNTA(A1:A50)-SUM(COUNTIF(A1:A50,E1:E9)))=0

お礼

ありがとうございます。
勉強になります。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

No.2です
もう一案
=AND(COUNTA(A1:A50)=50, ISNA(MATCH({"",1,2,3,4,5,"A","B","C","D","E"},A1:A50,0)))
そのまま確定、配列数式です。☆参考まで

お礼

なんどもありがとうございます。

回答No.3

zap35

あまり短くなりませんが、例えば
　=COUNTA(A1:A50)-COUNT(INDEX(MATCH(A1:A50,{1,2,3,4,5,"A","B","C","D","E"},0),))
ではいかがですか？

お礼

=COUNT(INDEX(MATCH(A1:A50,{1,2,3,4,5,"A","B","C","D","E"},0),))
で拾えるんですね、ありがとうございます。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

有無なので
=AND(A1:A50<>"",ISNA(MATCH({1,2,3,4,5,"A","B","C","D","E"},A1:A50,0)))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

お礼

やはり配列数式ですか。
"CSE 数式"はどうもぴんと来ないんですよ。
でもありがとうございます。

回答No.1

mt2008

DCOUNTで条件に該当する値が幾つあるかを調べて、データの総数から引いては如何でしょう。

A1に項目名を付けます(例：DATA)。次に、空いている列に一行目に先ほどと同じ項目名(例:DATA)、
2行目以降に除外したい値（A～E、1～5）を入れていきます。

D1:D11 に除外したい物のリストが出来たとして、条件に該当しないデータの個数は↓で出ます。
=COUNTA(A2:A21)-DCOUNT(A1:A21,,D1:D11)

お礼

ありがとうございます。
DCOUNTという関数があるんですね、勉強になります。