Excel Countifで否定条件を指定する方法

質問

こんにちは。たびたび質問させていただきます。
EXCELの集計で一点悩んでいます。
sheet2に以下のリストが存在します
　　A
1 　りんご
2　 みかん
3 ばなな
　　　・
　　　・
sheet1で次のように集計します
　　A　　｜　B
1　りんご　その他
2　　1　　　2
A2には、=COUNTIF(sheet2!$A$1:$A$100,A1)
を入れていますが、B2に対して、
　A1と一致しないもの　かつ
　ブランクでないもの
という条件を指定したいのですが、よい方法はありますでしょうか？
ご紹介いただけたら幸いです。

回答No.3

mshr1962 · ベストアンサー

A1に一致しないもの（A1がブランクの場合を含む）
=COUNTIF(sheet2!$A$1:$A$100,"<>"&A1)
ブランクのもの
=COUNTBLANK(sheet2!$A$1:$A$100)
上記二つをあわせて
=COUNTIF(sheet2!$A$1:$A$100,"<>"&A1)-IF(A1="",0,COUNTBLANK(sheet2!$A$1:$A$100))
で計算可能です。

配列数式を使う方法
=SUM(IF((sheet2!$A$1:$A$100<>A1)*(sheet2!$A$1:$A$100<>""),1))
と入力してCtrl+Shift+Enterで確定する。（確定後は式が{}で囲まれます。）

#2さんのSUMPRUDUCTの場合
=SUMPRODUCT((sheet2!$A$1:$A$100<>A1)*(sheet2!$A$1:$A$100<>""))
でも可

お礼

ありがとうございます。
次は、A1、A2、空白以外、という条件での関数が必要になりましたが、
別に質問させていただきます。

回答No.2

excel-jiten

SUMPRODUCT関数はいかがでしょうか。
B1に、

=SUMPRODUCT((sheet2!$A$1:$A$100<>$A$1)*NOT(ISBLANK(Sheet2!$A$1:$A$100)*1))

と入れてみてください。

参考URL：

http://www.excel-jiten.net/apply_funcs_001/cnt_multi_and_cnd.html

お礼

ありがとうございます。
また新規で新しい問題が浮上しました。
再度別件で質問させていただきます。
また機会ございましたらお願いいたします。

回答No.1

osamuy

A以外 = 総数 - Aの個数

だから、COUNTで総数を出して、A1でCOUNTIFした数を引くってのでどうでしょう。

お礼

ありがとうございます。
もっともわかりやすい方法ですね。
どこかに集計セルを作成し、そこを参照するようにしてみます。