Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique text in cell range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique text in cell range
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 "PhilH" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique text in cell range
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 "Mike H" wrote: 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 "PhilH" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique text | Excel Worksheet Functions | |||
Count unique if text | Excel Worksheet Functions | |||
Count unique items in range | Excel Worksheet Functions | |||
count by specific text color in range of cell | Excel Discussion (Misc queries) | |||
Unique count of text | Excel Worksheet Functions |