DSum with 3-D references

  • Thread starter Thread starter Paul Rogers
  • Start date Start date
P

Paul Rogers

Can you use 3-D references in DSum?

I can't get it to work,though it seems like it should.
For example I tried the formulae: =DSUM(Elect:Water!
$A$15:$Z$97,"9",Criteria!$D$17:$E$18)- but it wouldn't
calculate.

I would really appreciate any advice on this - rather than
having to do the two functions seperately.

Thanks

Paul
 
is "Elect:Water" the name of a sheet?
I think maybe the ":" would be causing the problem.

I'm using office 2k so i can't actually name a sheet
containing chars like ":".

The only other reason i can think why it isn't working is
if the criteria contain column titles that don't EXACTLY
match the column titles in your data.

John.
 
is "Elect:Water" the name of a sheet?
I think maybe the ":" would be causing the problem.
Click to expand...

The '3-D' part of the OP's subject line should have been a clue that the OP was
interested in 3D references, i.e., those that span multiple worksheets. The
colon operator is needed between worksheet names in 3D references.

If you don't know what 3D references are, you shouldn't respond to questions
concerning them.
 
Can you use 3-D references in DSum?
Click to expand...

No, and FWLIW, no other spreadsheet I'm aware of that provides DSUM equivalents
allows them to operate across multiple worksheets. Xess's CSUM may, but that's
getting a bit exotic.
I would really appreciate any advice on this - rather than
having to do the two functions seperately.
Click to expand...

Several 3D variations on SUMIF have been posted in the past, but like SUMIF
they're restricted to a single criterion. Your formula appears to use two
criteria. If there were only two worksheets involved, a formula using two
separate DSUM calls added together would recalc MUCH FASTER than any VBA udf you
might try using. However, if you really want a 3D DSUM, you could try the udf
below (see http://www.cpearson.com/excel/codemods.htm also).


Function DSUM3D(tul As Range, blr As Range, f As Variant, c As Range) As Variant
Dim i As Long, k As Long, n As Long, ra As String

If Not tul.Parent.Parent Is blr.Parent.Parent Then
DSUM3D = CVErr(xlErrRef)
Exit Function
End If

k = tul.Parent.Index
n = blr.Parent.Index

If k > n Then
i = k
k = n
n = i
End If

ra = tul.Range(tul.Address, blr.Address).Address(0, 0, xlA1, 0)

With tul.Parent.Parent.Worksheets

For i = k To n
DSUM3D = DSUM3D + _
Application.WorksheetFunction.DSum(.Item(i).Range(ra), f, c)
Next i

End With

End Function
 
Back
Top