COUNT Error (Bug?) - Any workarounds?

UniMord

Active Member
Joined
May 6, 2002
Messages
311
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
Maybe like this:
Code:
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))
 
Upvote 0
Corrected:
Code:
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.
 
Upvote 0
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!
 
Upvote 0
Hmm. Try this:
Code:
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
 
Upvote 0
It would probably be better implemented as a function that returns a range:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top