Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count a column "unique value only"
Is there a way to count the cell in a column, by unique value only.
I have a column with names of people. There are only 10 names over 400 rows. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count a column "unique value only"
On 28 jul, 20:23, Galway wrote:
Is there a way to count the cell in a column, by unique value only. I have a column with names of people. There are only 10 names over 400 rows. Hi Galway, If you know all the 10 names try: =CountIf(Range(),"A Name") eg: A 1 Abe 2 Bert 3 Charlie 4 Dirk 5 Edward 6 Abe 7 Charlie For B1: =COUNTIF(A1:A7,"Abe") the result will be 2 HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count a column "unique value only"
=SUMPRODUCT((A2:A200<"")/COUNTIF(A2:A200,A2:A200&""))
-- __________________________________ HTH Bob "Galway" wrote in message ... Is there a way to count the cell in a column, by unique value only. I have a column with names of people. There are only 10 names over 400 rows. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count a column "unique value only"
On Jul 28, 11:40*am, RadarEye wrote:
On 28 jul, 20:23, Galway wrote: Is there a way to count the cell in a column, by unique value only. I have a column with names of people. There are only 10 names over 400 rows. Hi Galway, If you know all the 10 names try: =CountIf(Range(),"A Name") eg: * * A 1 *Abe 2 *Bert 3 *Charlie 4 *Dirk 5 *Edward 6 *Abe 7 *Charlie For B1: =COUNTIF(A1:A7,"Abe") the result will be 2 HTH Thank you so far..however What if you do not know the names? Galway |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count a column "unique value only"
On 29 jul, 00:14, Galway wrote:
On Jul 28, 11:40*am, RadarEye wrote: On 28 jul, 20:23, Galway wrote: Is there a way to count the cell in a column, by unique value only. I have a column with names of people. There are only 10 names over 400 rows. Hi Galway, If you know all the 10 names try: =CountIf(Range(),"A Name") eg: * * A 1 *Abe 2 *Bert 3 *Charlie 4 *Dirk 5 *Edward 6 *Abe 7 *Charlie For B1: =COUNTIF(A1:A7,"Abe") the result will be 2 HTH Thank you so far..however What if you do not know the names? Galway- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hi Galway, If you do not know the names, and you dont want to mesh up your sheet try this macro: '-----------------Start Public Sub CountUniqueValues() ReDim strNames(0) As String ReDim intCount(0) As Long Dim lngLastRow As Long Dim lngUniqueNames As Long Dim rngSingle As Range Dim intLastUnique As Integer Dim intLoopUnique As Integer Dim blnNotFound As Boolean Dim strMessage As String intLastUnique = -1 lngLastRow = Cells(1, 1).End(xlDown).Row For Each rngSingle In Range(Cells(1, 1), Cells(lngLastRow, 1)).Cells blnNotFound = True If intLastUnique = -1 Then strNames(0) = rngSingle.Value intCount(0) = 1 intLastUnique = 0 Else For intLoopUnique = 0 To intLastUnique If strNames(intLoopUnique) = rngSingle.Value Then intCount(intLoopUnique) = intCount(intLoopUnique) + 1 blnNotFound = False Exit For End If Next If blnNotFound Then intLastUnique = intLastUnique + 1 ReDim Preserve strNames(intLastUnique) As String ReDim Preserve intCount(intLastUnique) As Long strNames(intLastUnique) = rngSingle.Value intCount(intLastUnique) = 1 End If End If Next strMessage = "Names" & vbTab & "Number" & vbNewLine For intLoopUnique = 0 To intLastUnique strMessage = strMessage & strNames(intLoopUnique) & _ vbTab & intCount(intLoopUnique) & vbNewLine Next MsgBox strMessage End Sub '------------END |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if "yes or no" in one range if there is a unique number in another | Excel Worksheet Functions | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Can "countifs" be utilized to return a count of unique values? | Excel Worksheet Functions | |||
Count letter"B" in one column based on unique value among duplicat | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |