Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count distinct value in column... (2000)

    In column Y (started from Y2) have:

    AAAAA
    AAAAA
    BBBBB
    BBBBB
    BBBBB
    VVVVV

    And admit have a var ="AAAAA" how to count in column Y only the value ="AAAAA" and store the result in var my_count?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count distinct value in column... (2000)

    You could use

    my_count = Application.WorksheetFunction.CountIf(Range("Y2:Y6 5536"), "AAAAA")

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count distinct value in column... (2000)

    Based this code to count distinct value in column, how to count based the variable "PUGLIA" in in column Y, all cell no blank in column AC... and return the result of count in X
    In this case X=6
    Attached sheet.
    Other way are welcome, sure.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count distinct value in column... (2000)

    You can use the following formula in a cell to calculate the count of rows where column Y contains "PUGLIA" and column AC is not blank:
    <code>
    =SUMPRODUCT((Y2:Y1000="PUGLIA")*NOT(ISBLANK(AC2:AC 1000)))
    </code>
    You can use VBA to get the value of the cell.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count distinct value in column... (2000)

    In VB you can do something like:

    <pre>Option Explicit
    Sub SALCounter()
    Dim rng As Range
    Dim var As String
    Dim rCell As Range
    Dim my_count As Integer
    var = "PUGLIA"
    With Worksheets("Luststp")
    Set rng = .Range(.Range("Y3"), _
    .Cells(.Cells.Rows.Count, "Y").End(xlUp))
    End With
    my_count = 0
    For Each rCell In rng
    If rCell.Value = var And _
    rCell.Offset(0, 4) <> "" Then
    my_count = my_count + 1
    End If
    Next
    End Sub</pre>


    you could also make it a custom function to feed the offset and string and column, sheet as parameters, but I will that to you...

    Steve

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count distinct value in column... (2000)

    GOOD CODE!
    Tks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •