Advanced Search

Summary Numerical Count

/ Published in: Visual Basic
Save to your folder(s)
Counts numbers and places the result in row 1. To get this populated click an top row (any) cell and click Yes to the following dialogue box. Only uniquely counts number values, not text.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. '="# Clients: " & SUM(IF(FREQUENCY(A3:A1000,A3:A2000)>0,1))
  3. Dim sDesc As String
  4. Dim sFormula As String
  5. Dim sColumnAlpha As String
  6.  
  7. On Error GoTo Exit_Worksheet_SelectionChange
  8.  
  9. If Target.Row = 1 Then
  10. If MsgBox("Count the unique numbers for this column?", vbYesNo + vbQuestion, "Uniquw Number Count") = vbNo Then Exit Sub
  11. sDesc = Target.Offset(1, 0).Value
  12. sColumnAlpha = Mid(Target.Address, 2, 1)
  13.  
  14. sFormula = _
  15. "=SUM(IF(FREQUENCY(" _
  16. & sColumnAlpha & "3:" _
  17. & sColumnAlpha & "10000," _
  18. & sColumnAlpha & "3:" & _
  19. sColumnAlpha & "10000)>0,1))"
  20.  
  21. Target.Formula = sFormula
  22. Target.Value = "Unique# " & sDesc & ": " & Target.Value
  23.  
  24. End If
  25.  
  26. Exit_Worksheet_SelectionChange:
  27. Exit Sub
  28.  
  29.  
  30.  
  31. Error_Proc:
  32.  
  33. Select Case Err.Number
  34.  
  35. Case 1004
  36. Resume Exit_Worksheet_SelectionChange
  37.  
  38.  
  39. End Select
  40.  
  41.  
  42.  
  43. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.