excel関数 参照したセルで始まるもの（同一ではなく）をカウントしたい

質問

お世話になっております！
例えば列Ａに国名があり、列Bには国名プラス数字が入っています。
・ Ａ ・ Ｂ ・ Ｃ
１・アメリカ ・ アメリカ4
２・カナダ ・ アメリカ3
３・ブラジル・ 中国1
４・中国 ・ 中国1
５・・ ブラジル8

（注：中点はセルの区切りを表すもので、実際には付きません）

Ｃ列にて、Ｂ列の、国別の数を数えたい（但し「中国1」のように国名・数字とも同じ場合は1とカウントする）のです。ですので正解はＣ１（アメリカ）に２、Ｃ２（カナダ）に０、Ｃ３（ブラジル）に１、Ｃ４（中国）に１と表示されるような関数を作りたいのです。
当ＨＰで教えて頂いた知識により、例えばＣ１に入れる式は
=SUMPRODUCT(1/COUNTIF(A:A,A1))
ここまでは思い付いたのですが、そうすると「アメリカ」しかカウントしてくれずゼロになってしまいます。
「Ａ１」と同一ではなく、「Ａ１」で始まるセルをカウントするにはどうしたらいいでしょうか？
長くなりすみません

回答No.2

tamazo- · ベストアンサー

配列数式を使って出来ました。
ただし、A5にも国名が入っている（空白ではない）前提です。
まず、C1に
=IF((LEFT(B1,LEN(A1))=A1)*(COUNTIF(B$1:B$5,B1)>1),1,SUM(IF(LEFT(B$1:B$5,LEN(A1))=A1,1,0)))
と入力して、ShiftとCtrlを押しながらEnterを押します。
これで、式の両側に{ }がついた状態になると思います。
次に、C1をコピーしてC2からC5までにコピーします。

式の意味としては、
Ｂの左からＡの文字数分がＡと同じで、しかもＢがＢ列の
なかで２つ以上あったら１にする。
そうではないばあい、Ｂ列の文字の左からＡの文字数分が
Ａと同じものの数をカウントする
というややこしいものになります。。。分かりづらいですが。

もし中国1を２とカウントしてよいなら、
=SUM(IF(LEFT(B$1:B$5,LEN(A1))=A1,1,0))
としてShift+Ctrl+Enterだけでよいのですが。

お礼

どうもありがとうございます！！
私の能力では理解するのにものすごく時間がかかりそうなので、一旦締め切って、ゆっくり考えます。スミマセン(>_<)
でも、私の長ったらしい説明を読んで、理解し、考えて下さったとは、本当にありがたいです。

回答No.1

elthy

力技で長いので申し訳ないですが、私なら単純に
=IF(LEFT(A1,2)=LEFT(B1,2),1,0)+IF(RIGHT(A1,1)=RIGHT(B1,1),1,0)
とします。国別で同じような国が幾つあるか、が問題になっちゃいますが。

お礼

どうもほんとうにありがとうございます！！
実はLEFT,RIGHTというの使ったことが無いのですが、この機会に勉強します。