指定文字以外のカウント

H列から指定した文字（東京）のカウントをすることは下記コードでできたのですが、H列から指定した文字（東京、神奈川、千葉、埼玉）以外をカウントするにはどのようにしたら良いか教えてください。
カウントした結果は、その他セルF4へ表示したいです。図ですとその他は４になります。

Sub sample()
Cells(4, 2) = WorksheetFunction.CountIf(Range("H4:H18"), "東京*")
End Sub

No.4 ベストアンサー 回答者： Qchan1962 回答日時： 2023/05/09 23:06

こんばんは

図のような表組みが決まっているのなら関数を使うのが簡単ですね
当然そのような事はお判りかと思います
B4：=COUNTIF($H4:$H18,B3&"*")　E4までフィル
F4：=COUNTIF(H4:H18,"<>")-SUM(B4:E4)
既に回答にありますね

VBAならば上記関数、手順を踏まえて
Sub test()
Dim keyRng As Range
Dim popRng As Range
'3行目４行目の右列が空いていれば
'Set keyRng = Range("B3", Cells(3, Columns.Count).End(xlToLeft))
Set keyRng = Range("B3:F3")
Set popRng = Range("H4", Cells(Rows.Count, "H").End(xlUp))
Dim r As Range, n As Long
For Each r In keyRng
r.Offset(1) = WorksheetFunction.CountIf(popRng, r.Value & "*")
n = n + r.Offset(1)
Next
n = n + WorksheetFunction.CountIf(popRng, "")
Set r = keyRng.Find(What:="その他", LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then r.Offset(1) = popRng.Cells.Count - n
End Sub

For Eachはフィルのかわり？　その他を1度無駄に出力していますが・・・
変数ｎは累計　SUMのかわり、 n + r.Offset(1)　r.Offset(1)は変数の方がいい？
CountIf(popRng, "")　空白の数を累計に+
１列なのでCells.Count、から累計を引くと　解

No.3 回答者： duffer_dog 回答日時： 2023/05/03 18:13

総当り式に調べて各地域をカウントしてみます。

H 列の地域数は1000行目まで増やせます。またカウントする地域名が変わってもこのままカウントできます。カウントする地域を増やすことも出来ます。かなり自由度が高いと思います。

Sub sumingup()
Dim FoundCell As Range
Dim l As Integer ' 地域列の最終行
Dim c As Integer ' 地域列の検査する地名
Dim s As Integer ' 発生数行の値
Dim i

s = 0
With ActiveSheet
l = .Cells(1000, 8).End(xlUp).Row
For i = 4 To l
' 集計する地名の列から地域を探す
Set FoundCell = .Range("3:3").Find(What:=.Cells(i, 8))
If FoundCell Is Nothing Then
' 集計地域ではない場合"その他"にまとめる
c = .Range("3:3").Find(What:="その他").Column
Else
' 集計地域列の列数を
c = FoundCell.Column
End If
' 直前の値を退避させる
s = .Cells(4, c)
' １を足す
.Cells(4, c) = s + 1
Next
End With
End Sub

この回答へのお礼

ありがとうございます。

お礼日時：2023/05/03 18:42

No.2 回答者： tatsumaru77 回答日時： 2023/05/03 15:08

総数からB4:E4の数を引きます。

Cells(4, 6) = WorksheetFunction.CountA(Range("H4:H18")) - WorksheetFunction.Sum(Range("B4:E4"))

この回答へのお礼

ありがとうございます。

お礼日時：2023/05/03 18:41

No.1 回答者： fujillin 回答日時： 2023/05/03 14:56

こんにちは

シート関数を使うのなら、直接関数式を設定してしまう方が簡単そうな気もしますが・・

ひとまず、ご提示の方法に倣って書くなら、
Range("F4").Value = Range("H4:H18").Cells.Count - Application.Sum(Range("B4:E4"))
みたいな感じでしょうか。
（B4:F4の計算が終わってから、実行する必要があります）

なお、
＞図ですとその他は４になります。
そのまま上記で計算すると、結果は「5」になります。
理由は、C4の値が2になっているから。
（「含む」でカウントするならC4の値は 3になると思いますが、ご提示の表では2となっていますので。）

※ H4:H18には空白セルは無いものと仮定しています。
空白セルを含んでかつそれはカウントしたくないという場合は、こちらもCOUNTAなどで算出する必要があるでしょう。

この回答へのお礼

ありがとうございます

お礼日時：2023/05/03 18:41