Counting with spaces

[Tom G]

Hi,

How do I count a total of things with spaces inbetween?

EX:

4/3/01 6

4/4/01 7

5/5/01 8
5/5/01 9
5/5/01 7

5/6/01 2

The answer I'm looking for is 4. Each date would represent a total of
one. The three entries on 5/5 would be counted as one.

Thanks,

Tom

 

[jlepack]

Sub numberOfGroups()
Dim r As Range, x As Integer
Set r = Range("A1")
x = 0
If r.Value = "" Then Set r = r.End(xlDown)
Do
If r.Offset(1, 0).Value = "" Then x = x + 1
Set r = r.End(xlDown)
Loop While r.Row < 65536
If Not r.Value = "" Then x = x + 1
MsgBox "Number of groups = " & x, , "Leppy's Code"
End Sub

This little sub should get you on your way to however you want to use
this.

Cheers,
Jason Lepack

 

[Tom G]

Try this:

This formula counts the unique items in A1:A10
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Adjust range references to suit your situation.

Does that help?

Yes it works just fine, thank you.

Tom