【頭の良い人へ】同セル内の文字列のカウントについて詳しい方教えてください

A1のセル内に　NG5,NG9,NG10,NG11,NG13　と入力されています。
A1のセル内のNG1を含む値を計算したく、

=COUNTIF(A1,"*NG1*") と計算式を入力したところ、3件とカウントしてほしいところ、
1件とカウントされてしまいます。頭1件しかカウントされていないようです。

どうすれば、正しい値を求めることができるでしょうか？
非常に困っています、助けてください。

質問者からの補足コメント

• 

  すみません、特定のセルから特定の範囲に広げた場合どうでしょうか？
  
  A1からA10内のNG1をカウントしたいです。回答いただいた式を
  範囲にひろげてもうまくいきません。
  
  =SUM(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"NG1","")))/LEN("NG1")
  
  何度も確認して申し訳ないですが、教えてください。
  ※質問の仕方勉強になりました。ありがとうございます。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/05/26 13:19

• 

  >No.7さん　カウントうまくいきました。ありがとうございます。
  条件がもう1個追加になってしまいました。
  
  B列にグループの表示があり、グループAかつ、A1からA10内のNG1の条件のものをカウントしたいです。そのようなことも可能でしょうか？
  
  お手数をおかけしますが、回答ください。
  よろしくお願いします。

    補足日時：2016/05/30 14:35

No.12 ベストアンサー 回答者： enunokokoro 回答日時： 2016/05/30 17:48

No.3とNo.7の回答者です。

> B列にグループの表示があり、
「グループの表示」とは？　具体的にはどのような表示なの？

>グループAかつ、A1からA10内のNG1の条件のものをカウント
この場合の「グループA」とは？

No.3の回答にある関数などで、何が行われているのか理解できれば、
質問者さんがある程度修正することも可能だと思います。

=LEN(A1)
で、A1セルなどの文字数を取得できます。

=(SUMPRODUCT(LEN(A1:A10))
で、A1からA10までの総文字数を総文字数を取得できます。

=LEN(SUBSTITUTE(A1"NG1",""))
で、A1セルなどから指定文字"NG1"を引いた文字数を取得できます。

=SUMPRODUCT(LEN(SUBSTITUTE(A1:A10,"NG1","")))
で、A1からA10までの各セルにある指定文字"NG1"を引いた総文字数
を取得できます。

総文字数から各セルの指定文字を引いた総文字数で、範囲指定した
セルの指定文字がない場合の文字数が取得できます。
取得できた文字数から指定文字の文字数で割った数が、カウントする
セル数となります。

No.5の回答者さんが書いた式は、各セルで指定文字を引いた文字数
をSUMPRODUCT関数で範囲指定した合計が出せる方法です。
これを指定文字の文字数で割ったものですから、私が示した式よりも
無駄がないものになっていますよね。

グループAとかが何を意味しているのかによりますが、○○かつ××の
指定は、一般的にIF関数とAND関数の組み合わせを使います。
http://www.relief.jp/itnote/archives/003169.php
これをNo.3やNo.5・No.7などの式を組み合わせれば、カウントできると
思います。

No.13 回答者： enunokokoro 回答日時： 2016/05/31 00:20

No.12の回答で回答者のナンバーを書き間違えていましたね。

No.5ではなくNo.6の回答ですね。
No.5の回答も参考になりますから、どれを採用するかの違いですね。

紛らわしい書き方をNo.12の回答でしたことを謝ります。

No.11 回答者： ママチャリ 回答日時： 2016/05/27 19:48

ユーザ定義関数（MyCountIf)で実装してみました。

適当なセルに、=MyCountIf(A1:A10,"*NG1*")　と入力します。

Function MyCountIf(MyRng As Range, MyStr As String) As Long
Dim r As Range
Dim c As Variant
For Each r In MyRng
For Each c In Split(r.Value, ",")
If c Like MyStr Then MyCountIf = MyCountIf + 1
Next
Next
End Function

No.10 回答者： tom04 回答日時： 2016/05/26 21:37

こんばんは！

横からお邪魔します。
＞A1からA10内のNG1をカウントしたいです。
VBAになりますが、一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から//
Dim c As Range, cnt As Long, myCnt As Long, myStr As String
myStr = InputBox("検索文字を入力")
For Each c In Selection
If InStr(c, myStr) > 0 Then
cnt = (Len(c) - Len(Replace(c, myStr, ""))) / Len(myStr)
myCnt = myCnt + cnt
End If
Next c
MsgBox myCnt
End Sub 'この行まで//

※　必ず範囲指定してマクロを実行してみてください。m(_ _)m

この回答へのお礼

ありがとうございました。
解決できました。

お礼日時：2016/06/07 14:33

No.9 回答者： くんこば 回答日時： 2016/05/26 19:42

完全に、質問内容を見間違えていました。

この場合、私なら自作関数を作成しますね。
Instr関数でヒット数を返却する感じかな。

No.8 回答者： CoalTar 回答日時： 2016/05/26 16:02

頭の回転は良くないので

1. A1:A10セルを選択
2. データ - 区切り位置
　ウィザードの2番目で[レ]カンマ にチェック
3. =COUNTIF(A1:G10,"NG1*")
　カウントする範囲は適宜設定のこと

No.7 回答者： enunokokoro 回答日時： 2016/05/26 14:33

No.3の回答者です。

No.4の回答者さんが書いているようにSUM関数は今回は不要でした。
No.5の回答者さんが書いているように、複数セルの総文字数をカウント
できれば、対応できると思います。


※No.5さんが提示している以外のINDEX関数を使う方法。
=(SUM(INDEX(LEN(A1:A10),))-SUM(INDEX(LEN(SUBSTITUTE(A1:A10,"NG1","")),)))/LEN("NG1")

No.6の回答者さんが提示しているSUMPRODUCT関数を使う方法。
=(SUMPRODUCT(LEN(A1:A10))-SUMPRODUCT(LEN(SUBSTITUTE(A1:A10,"NG1",""))))/LEN("NG1")
「/LEN("NG1")」の前全体を括弧で括らないと割る位置が違ってきます。
たぶん等号記号直後の括弧を忘れているだけだと思います。

No.6 回答者： WindFaller 回答日時： 2016/05/26 13:59

後出しすみません。

これで出来なかったら、誰か他の回答待ってください。

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"NG1",""))))/LEN("NG1")

No.5 回答者： d-q-t-p 回答日時： 2016/05/26 13:27

> A1からA10内のNG1をカウントしたいです。

計算を分ければいいだけだだと思います。
Excel2016なら COCATか TEXTJOIN関数で範囲を繋いでから 同じように処理すれ
ばいいでしょう。

今回の場合なら 古いバージョンでも
=(LEN(PHONETIC(A1:A10))-LEN(SUBSTITUTE(PHONETIC(A1:A10),"NG1","")))/3

No.4 回答者： GooUserラック 回答日時： 2016/05/26 13:02

enunokokoroさん、発想が良いですね！

でも、今回は「SUM」は不要だと思います。