エクセル COUNTIFで複数条件を表現する方法

質問

はじめまして。

A1～A10に国名があるとします。
　| A
--|---------
1| アメリカ
2| ドイツ
3| カナダ
4| イギリス※
5| フランス
6|
(略)
11|=COUNTIF(A1:A10,{"*","<>*※"})

この範囲の中で、'※'記号の無い国をカウントしたいのですが、
範囲中に、空白もある可能があり、
>COUNTIF(A1:A10,"<>*※")
とすると、空白もカウントされてしまいました。
なので
>COUNTIF(A1:A10,{"*","<>*※"})
としたのですが、期待する数字が返ってきません。
下の場合、「４」と言う数字が欲しいのですが
どなたかご教授願います。

回答No.6

EL-SUR · ベストアンサー

No.5です。

>作業列を使わない方法があれば助かるのですが、

もうご覧にならないかもしれませんが、次のような式なら一応可能です。
範囲が A1：A20だとして
=SUM(IF((LEN(A1:A20)*(RIGHT(A1:A20)<>"※")),1/COUNTIF(A1:A20,A1:A20)))

配列数式です。
Ctrl＋Shftキーを押しながら Enterで確定してください。
数式が{ }で囲まれます。
{　}で囲まれないときは、
F2キーを押してから、Ctrl＋Shift＋Enter してみてください。

配列数式は範囲が広くなると重くなります。
また、1/COUNTIF で、重複を除くカウントをするのは、小数計算のため誤差が出る可能性もないとは言えません。

お礼

ご丁寧にありがとうございます。
作業列を作らず出来そうなので、助かります。
是非参考にさせていただきます。
本当にありがとうございました。

回答No.7

imogasi

配列数式ですが、（既に出てますが）考え付く形を素直に配列数式に組み立てると
=SUM(IF((ISERROR(FIND("*",A2:A10))*(A2:A10<>"")),1,0))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲの３つのキーを同時に押す。
例　Ａ１：Ａ１０で

s
e
a
f
d
a*
c
C*
a*
で、
結果は６
＊のついていない国のグループに重複したものはないとしていて、除いてはいませんが。
式の意味は
ISERROR(FIND("*",A2:A10))は＊が見つからない
＊はＡＮＤ条件（掛け算になる）
A2:A10<>""は空白でない
該当すれば１（件として）を足す

お礼

ご回答ありがとうございます。
先のEL-SUR様同様、分り易い解説も記載して頂きとても勉強になりました。

回答No.5

EL-SUR

No.4です。

イギリス※ が2個以上あるように
同じ国名で ※がつくものが重複する場合ですが、
どうせ作業列を使うなら、

B1：
=IF((RIGHT(A1)<>"※")*(COUNTIF($A$1:A1,A1)=1),1,"")

下にコピー

カウントは、B列を SUM関数で合計すればすむ話でしたね、すみません。
=SUM(B:B)

お礼

ご丁寧な返信ありがとうございました。

>条件は最初の質問で出していただくようお願いします。
お手数をお掛けしてしまいました。すみませんでした。

>※のつく国名が重複することもあるんでしょうか？
>イギリス※ が 2回出てくるとか。
はい。あります。

正直申し上げますと、作業列を使わない方法があれば助かるのですが、
自力では無理っぽいので、EL-SUR様からお教え頂いた方法を使わせていただきます。
どうもありがとうございました。

回答No.4

EL-SUR

No.2です。

条件は最初の質問で出していただくようお願いします。

※のつく国名が重複することもあるんでしょうか？
イギリス※ が 2回出てくるとか。

そういうのがなければ↓でどうでしょう？
=COUNT(INDEX(1/(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)),0))-COUNTIF(A1:A10,"*※")

検索範囲が 2行目以下から始まる場合も、
ROW(A1:A10) の引数は A1から始めてください。
範囲が A2：A100 なら ROW(A1:A99) のように。

>=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
↑だと空白セルがあればエラーになりませんか？

もし イギリス※ のようなものも重複しているなら、
たとえば B列を作業列にして、
B1：
=IF((RIGHT(A1)="※")*(COUNTIF($A$1:A1,A1)>1),1,"")

A列のデータ最終行までコピー

重複を除いたカウントは↓
=COUNT(INDEX(1/(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)),0))-COUNTIF(A1:A10,"*※")+SUM(B:B)

回答No.3

hidechan2004

空白行はCOUNTBLANK（A1:A10)で求められますので、
出てきた答えから引けば問題ないと思います。

お礼

ご回答ありがとうございます。
今回は、重複しているデータ数も省きたいので、次回の参考にさせていただきます。

回答No.2

EL-SUR

こんなのでどうでしょう p（．．）

=COUNTIF(A1:A10,"*?")- COUNTIF(A1:A10,"*※")
とか

=SUMPRODUCT((A1:A10<>"")*(RIGHT(A1:A10)<>"※"))

補足

ご回答ありがとうございます。

実は、例で挙げた国名が重複している場合があり、
その場合は重複している国は一としてカウントしたいので既に

>=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))-IF(COUNTBLANK(A1:A10)>0,1,0)

と言う記述をしています。
で、今回は
>COUNTIF(A1:A10,A1:A10)
の検索条件を変更すれば良いかなと思ったのですが、
どうも上手くいきません。

またご意見お願いいたします。

回答No.1

ysko614

考え方を変えて、空白のある行を求めてはどうですか？

全体のデータのカウント数と範囲内の数の差が空白ですよね？あるいは、=COUNTIF(A1:A10,"")とすれば空白のセルの数が出てきます。

それと最初求めた確実に出てくる数を足せばいいと思います。

お礼

ご回答ありがとうございます。
今回は、重複しているデータ数も省きたいので、次回の参考にさせていただきます。