limits for DSUM( ) ?

  • Thread starter Thread starter Nancy
  • Start date Start date
N

Nancy

Does anyone know of limits in using DSUM()? I could need
approx. 400 separate DSUM statements in a single
spreadsheet. The results of DSUM statements could be
embedded in formulas nested several layers deep.

Also would like to hear of any known pitfalls using DSUM.

thanks!
 
I hardly ever use it.. One pitfall that I know of is that it
won't work on closed workbooks. The only bonus I know it that is faster
than sumproduct..
 
I hardly ever use it.. One pitfall that I know of is that it
won't work on closed workbooks. The only bonus I know it that is faster
than sumproduct..
Click to expand...
...

Possibly slower than SUMPRODUCT is SQL.REQUEST, but it'll work against closed
files, and it's a heck of a lot more flexible than DSUM. See Andy Wiggins's site
for details.

http://www.BygSoftware.com

What, precisely, are you trying to do? It's possible that a combination of one
DSUM formula used in conjunction with a data table may be sufficient to meet
your needs, but without details it's impossible to be certain.
 
Harlan, just wanted to be sure I'm not missing something.
I need many different answers from the "database" so I'll
need many DSUM() statements (each having different
criteria). The results of the DSUM() statements are then
used in combination for further calculations. Does that
help?
 
Harlan, just wanted to be sure I'm not missing something.
I need many different answers from the "database" so I'll
need many DSUM() statements (each having different
criteria). The results of the DSUM() statements are then
used in combination for further calculations. Does that
help?
Click to expand...

Many different criteria could mean different things. If the fields would vary
for many of these DSUMs, then my suggestion for using a data table wouldn't
work. However, if you're always looking at the same fields but just matching
different values in those fields, then a data table would work.

For example, with the following in A1:D10

Reg Qtr Prd Sales
NW 1 A 10
NW 1 B 20
NW 2 A 30
NW 2 B 40
SW 1 A 50
SW 1 B 60
SW 2 A 70
SW 2 B 80
SW 2 C 90

And you wanted to get total sales by region and quarter, you could use a
criteria range, say in G1:H2, looking like

Reg Qtr
NW 2

Then the result would be =DSUM($A$1:$D$10,"Sales",$G$1:$H$2), which would return
70. You'd need 4 separate DSUM formulas along with 4 separate criteria ranges to
handle both regions and both quarters. The total number of DSUM formulas grows
as the product of the number of distinct entries in each field you'd use as
criteria.

In the example above, you could enter NW and SW in K2:K3, 1 and 2 in L1:M1, and
the formula =DSUM($A$1:$D$10,"Sales",$G$1:$H$2) in K1, then select K1:M3 and run
Data > Table..., specify H2 as the Row Input Cell and G2 as the Column Input
Cell, and Excel will fill out L2:M3 with the sales by region and quarter. Data
tables could handle more complicated criteria, but you'd need to provide
details. Pivot tables would also do this, and may be even better depending on
your precise needs.

I'd bet there's a way for you to avoid needing hundreds of DSUM formulas with
hundreds of different criteria ranges, but it'd require pure guesswork to give
you any further advice without you providing details. If you don't want to give
details, you're on your own.
 
Back
Top