Count unique text in cell range

[PhilH]

In the range C5:C123, I want to count the number of times L31, L311, L316,
and L318, and blanks appear, and have the information appear like this:

L31 14
L311 24
L316 45
L318 33
Blank 3
Sum 119 (the total number of cells in the range)

How would this be done?

 

[Mike H]

Hi,

You should be able to modift this for all your L numbers

="L31 = "&COUNTIF(C5:C123,"L31")

and for the blanks

="Blanks = "&COUNTBLANK(C5:C123)

Mike

 

[AndrewCerritos]

Here is my take to use VBA.
Result is printed via Debug.print statement.

Private Sub CountUnique()
Dim rngA As Range
Dim varTXT As Variant
Dim varCNT() As Long
Dim iX As Long
Dim nSUM As Long

varTXT = Array("L31", "L311", "L316", "L318", "")
Set rngA = ActiveSheet.Range("C19:C123")
nSUM = 0
ReDim varCNT(LBound(varTXT) To UBound(varTXT))
For iX = LBound(varTXT) To UBound(varTXT)
varCNT(iX) = WorksheetFunction.CountIf(rngA, varTXT(iX))
If varTXT(iX) = "" Then
Debug.Print iX; "Blank", varCNT(iX)
Else
Debug.Print iX; varTXT(iX), varCNT(iX)
End If
nSUM = nSUM + varCNT(iX)
Next iX
Debug.Print "Sum", nSUM
End Sub