COUNT Error (Bug?) - Any workarounds?

[UniMord]

COUNT double counts on overlapping ranges.

For example (assuming all cells contain numbers).

=COUNT(A1:J1, A1:J1) returns 20 instead of 10.
=COUNT(A1:B1, A1:A2) returns 4 instead of 3.
​

Now, I know most functions behave this way (SUM for instance), but with COUNT, we're looking for the count in all the ranges together, it shouldn't be double counting. For the record, the status bar gives the correct count.

Is there a practical workaround?

For just 2 ranges, we can use:

=COUNT(A1:B1, A1:A2) - COUNT(A1:B1 A1:A2)
​

But it's downhill once we hit 3 ranges or more.

 

[Richard Schollar]

COUNT works the way I would expect - you just need to not use overlapping ranges.

 

[UniMord]

The status bar gives the actual count, without double counting, so it seems that Excel understands that it's useful to know the actual count. Which is why I expected COUNT to work that way as well.

Supposing you did need the actual count, how would you go about it?

I'm using COUNT in a VBA application where the users have to be permitted to select however they choose. I could always resort to a loop, but I was checking if there's a faster, built-in way of going about this. I hate reinventing the wheel. Wheels were never my thing, anyway.

 

[shg]

Maybe like this:

Function MyCount(rInp As Range)
    Dim rCnt        As Range
    Dim rArea       As Range
    Dim cell        As Range
 
    Set rCnt = rInp.Areas(1)
 
    For Each rArea In rInp.Areas
        If Intersect(rArea, rCnt) Is Nothing Then
            Set rCnt = Union(rCnt, rArea)
        Else
            For Each cell In rArea
                If Intersect(rArea, rCnt) Is Nothing Then
                    Set rCnt = Union(rCnt, cell)
                End If
            Next cell
        End If
    Next rArea
 
    MyCount = WorksheetFunction.Count(rCnt)
End Function

Multi-area ranges need to be passed in parens:

=MyCount((A1:A6, A3:E3, C1:C6))

 

[shg]

That doesn't work ...

 

[shg]

Corrected:

Function MyCount(rInp As Range) As Long
    Dim rCnt        As Range
    Dim rArea       As Range
    Dim cell        As Range
 
    Set rCnt = rInp.Areas(1)
 
    For Each rArea In rInp.Areas
        If Intersect(rArea, rCnt) Is Nothing Then
            Set rCnt = Union(rCnt, rArea)
        Else
            For Each cell In rArea
                If Intersect(cell, rCnt) Is Nothing Then
                    Set rCnt = Union(rCnt, cell)
                End If
            Next cell
        End If
    Next rArea
    
    MyCount = WorksheetFunction.Count(rCnt)
End Function

Depending on the complexity of the ranges passed, this could be made faster by unioning all the disjoint areas first, putting the intersecting ranges into a collection to be processed cell by cell at the end. In this example,

=MyCount((A1:A6, A3:E3, C1:C6))

... the second two ranges have to be processed as cells. In the alternative, only the second would be.

 

[UniMord]

Thanks! Your function works as advertised.

As you can guess, if the ranges are sizeable, the computing overhead will be substantial. (2 Rows & 2 Columns took 14 seconds on my computer.)
But, it's the only option I'm aware of.

Thanks again!

 

[shg]

Hmm. Try this:

Function MyCount(rInp As Range) As Long
    Dim rCull       As Range
    Dim rCnt        As Range
    Dim rArea       As Range
    Dim cell        As Range
 
    Set rCull = Intersect(rInp, rInp.Worksheet.UsedRange)
    Set rCnt = rCull.Areas(1)
 
    With New Collection
        For Each rArea In rCull.Areas
            If Intersect(rArea, rCnt) Is Nothing Then
                Set rCnt = Union(rCnt, rArea)
            Else
                .Add Item:=rArea.Address(External:=True)
            End If
        Next rArea
 
        Do While .Count
            For Each cell In Range(.Item(1))
                If Intersect(cell, rCnt) Is Nothing Then
                    Set rCnt = Union(rCnt, cell)
                End If
            Next cell
 
            .Remove 1
        Loop
    End With
 
    MyCount = WorksheetFunction.Count(rCnt)
End Function

 

[UniMord]

Instantaneous.
Thanks man!

 

[shg]

It would probably be better implemented as a function that returns a range:

Function rUnique(rInp As Range) As Range
    Dim rCull       As Range
    Dim rArea       As Range
    Dim cell        As Range
 
    Set rCull = Intersect(rInp, rInp.Worksheet.UsedRange)
    Set rUnique = rCull.Areas(1)
 
    With New Collection
        For Each rArea In rCull.Areas
            If Intersect(rArea, rUnique) Is Nothing Then
                Set rUnique = Union(rUnique, rArea)
            Else
                .Add rArea.Address(External:=True)
            End If
        Next rArea
 
        Do While .Count
            For Each cell In Range(.Item(1))
                If Intersect(cell, rUnique) Is Nothing Then
                    Set rUnique = Union(rUnique, cell)
                End If
            Next cell
 
            .Remove 1
        Loop
    End With
End Function

Then you can do

=COUNT(rUnique((A:A,3:3, C:C)))

=SUM(rUnique((A:A,3:3, C:C)))

... etc.

The double parens could be eliminated with a ParamArray, but that would require a bunch of error checking.