# Thread: Count distinct value in column... (2000)

1. ## 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. ## Re: Count distinct value in column... (2000)

You could use

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

3. ## 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. ## 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. ## 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. ## 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
•