件数の求め方

質問

下図のようなシートがあります。A列：部署名、B列：役職名、C列：部署番号、D列：基幹職No、E列：氏名、F列：基幹職人数、G列：一般職人数、H列：合計人数　と配置されています。ここで各部署ごとの基幹職人数・一般職人数・合計人数をそれぞれ求めたいのです。
基幹職人数(図の黄色のセル)を求めるには基幹職No.の入っている個所をカウントするので、以下の通りとなると思うのですが
Sub macro4()
'
' Macro 4 Macro
'
Dim Gyo As Integer

Application.DisplayAlerts = False

Sheets("WORK").Select
Gyo = 2
Do While Cells(Gyo, 3) <> ""
If Cells(Gyo, 3) <> "" Then
If Cells(Gyo, 4) <> "" Then
Cells(Gyo, 6).Select
ActiveCell.Formula = "=counta(""D"" & Gyo & "":"" & ""D"" & Gyo)"
End If
End If
Gyo = Gyo + 1
Loop
Range("A1").Select
End Sub

一般職人数及び合計人数を求める場合のコーディングが分かりません。
因みに一般職はD列：基幹職Noが空白となっている個所であり、一般職人数のセルは部署番号が変わる1つ前の行のG列（図の水色のセル)、合計人数も部署番号が変わる1つ前の行のH列（図の橙色のセル)に出力するものとしたいのです。このようなことが出来ますでしょうか。
なお条件として、
（１）組織変更が発生する都度、部署番号・基幹職No・人数も変わる
（２）合計人数は部署番号ごとの基幹職人数＋一般職人数とする
であります。どうかご教授の程宜しくお願い致します。

回答No.2

ushi2015 · ベストアンサー

こんにちは
Sub test1()
　　Dim r As Range
　　Const c1 As String = "C:C"
　　Const c2 As String = "D:D"
　　Const k As Long = 2
　　Const i As Long = 3
　　Const g As Long = 4

　　For Each r In Range(c2).Cells(2, 1).Resize(Range(c1) _
　　　　　　　　　　.SpecialCells(xlCellTypeConstants).Cells.Count - 1)
　　　　If r.Value <> "" Then
　　　　　　r.Offset(, k).Formula = _
　　　　　　　　"=COUNTIFS(" & c1 & "," & Intersect(r.EntireRow, Range(c1)).Address & _
　　　　　　　　　　"," & c2 & "," & r.Address & ")"
　　　　ElseIf r.Offset(, -1).Value <> r.Offset(1, -1) Then
　　　　　　r.Offset(, i).Formula = _
　　　　　　　　"=COUNTIFS(" & c1 & "," & Intersect(r.EntireRow, Range(c1)).Address & _
　　　　　　　　　　"," & c2 & ","""")"
　　　　　　r.Offset(, g).Formula = _
　　　　　　　　"=COUNTIF(" & c1 & "," & Intersect(r.EntireRow, Range(c1)).Address & ")"
　　　　End If

　　Next
End Sub
で。

回答No.1

ushi2015

こんにちは
Sub test()
　　Dim r As Range
　　Sheets("WORK").Select
　　For Each r In Range("D2", Range("A" & Rows.Count).End(xlUp).Offset(, 3))
　　　　If r.Value <> "" Then
　　　　　　r.Offset(, 2).Formula = _
　　　　　　　　"=COUNTIFS(A:A," & r.Offset(, -3).Address & _
　　　　　　　　　　",D:D," & r.Address & ")"
　　　　ElseIf r.Offset(, -3).Value <> r.Offset(1, -3) Then
　　　　　　r.Offset(, 3).Formula = _
　　　　　　　　"=COUNTIFS(A:A," & r.Offset(, -3).Address & _
　　　　　　　　　　",D:D,"""")"
　　　　　　r.Offset(, 4).Formula = _
　　　　　　　　"=COUNTIF(A:A," & r.Offset(, -3).Address & ")"
　　　　End If

　　Next
End Sub
こういう事ですか？

補足

ushi2015さん、今晩は。
早速のご回答頂き有難うございます。結果としては記述頂いたコーディングでバッチリでした。
因みにこのコーディングではA列（部署名）をキーにCOUNTIFS関数で件数を出すようにしていますが、これをC列（部署番号）をキーにCOUNTIFS関数で処理をさせたい場合はどうしたら良いでしょうか。
実は本番のデータを使用した時にA列（部署名）が同じ部署名にも関わらずセルによっては全角記述だったり全角半角混在する記述（例：システム開発部の「システム」が全角記述又は半角記述）がみられ、結果として部署番号が同一でも別べつの部署扱いとなって件数が出力される現象が出ているのです。
お手数かけて済みませんが、何卒再度ご教授の程宜しくお願い致します。