Major design flaw in Union "operator"

[JenniferMurphy]

I just discovered what I consider a major flaw in the design of the Union (comma) operator. As illustrated in the workbook here:

https://www.dropbox.com/sh/v0799qr2uex4imf/AAAWi4Jhq4-vfCmuQTn4qo4Za?dl=0

The Sum function, when applied to a union of overlapping ranges, will sum the values in the overlapping area multiple times. Mathematically, this is nonsense. What possible rationale could the geniuses at M$FT possibly have had in mind?

I am inclined to agree with opinions expressed here,

http://dailydoseofexcel.com/archives/2005/01/16/union-and-intersect/

that they just had this "operator", the comma, already implemented and decided to call it an operator and ignore the fact that it is mathematically wrong.

I would love to hear a compelling defense of this design.

PS: It looks like a couple of my personal UDFs, which I moved to a code module, may not be working in the workbook. My apologies if that's true. I would also appreciate any hints of how I can make that work when I send workbooks to other people that make use of any of my personal add-in code.

 

[footoo]

Here's a UDF :

Public Function USUM(rng As Range)
Dim area As Range, uRng As Range
For Each area In rng.Areas
    If uRng Is Nothing Then
        Set uRng = area
    Else
        Set uRng = Application.Union(uRng, area)
    End If
Next
USUM = Application.WorksheetFunction.Sum(uRng)
End Function

Enter on the worksheet like this :
=USUM((K1:K6,K5:K11))

It is not necessary to use Application.Union
This also works :

Public Function USUM(rng As Range)
Dim area As Range, uRng As Range
Set uRng = rng.Areas(1)
For Each area In rng.Areas
    Set uRng = Range(uRng, area)
Next
USUM = Application.WorksheetFunction.Sum(uRng)
End Function