Definitely the fastest way to count the 'inactives' and 'Bads':

Sub M_snb()
  sp = Sheet2.ListObjects(1).DataBodyRange
  sn = Sheet3.ListObjects(1).DataBodyRange
    
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sp)
      .Item(sp(j, 2)) = sp(j, 17)
    Next
        
    For j = 1 To UBound(sn)
      sn(j, UBound(sn, 2)) = 0
      sn(j, UBound(sn, 2) - 1) = 0
           
      For jj = 6 To 22
        If .exists(sn(j, jj)) Then
          If .Item(sn(j, jj)) = "Inactive" Then sn(j, UBound(sn, 2) - 1) = sn(j, UBound(sn, 2) - 1) + 1
        ElseIf sn(j, jj) <> "X" And sn(j, jj) <> "" Then
          sn(j, UBound(sn, 2)) = sn(j, UBound(sn, 2)) + 1
        End If
      Next

      If sn(j, UBound(sn, 2)) = 0 Then sn(j, UBound(sn, 2)) = ""
      If sn(j, UBound(sn, 2) - 1) = 0 Then sn(j, UBound(sn, 2) - 1) = ""
    Next
  End With
    
  Sheet3.ListObjects(1).DataBodyRange = sn
End Sub