Help with DSUM

[pmcmicha]

I only want to sum the contents of a paticular column based on certain criteria which changes throughout the history of the worksheet. Below is what I have setup for DSUM, but I cannot seem to get this to work properly. Either I am using the function incorrectly or this is the wrong function to use.

============================================================
Sheet: DATA (FIELDS, A124125)

LEVEL: MIN_LEVEL: MAX_LEVEL:
COL7 >0 <3
============================================================
Sheet: CP (FIELDS, A41:AE250)

LEVEL: COL2: COL3: COL4: COL5: COL6: COL7
0 xxxx xxxx xxxx xxxx xxxx 4
1 xxxx xxxx xxxx xxxx xxxx 5
2 xxxx xxxx xxxx xxxx xxxx 6
3 xxxx xxxx xxxx xxxx xxxx 7
4 xxxx xxxx xxxx xxxx xxxx 8
============================================================

Since the MAX_LEVEL: changes throughout the use of this sheet based on other criteria, it links to another cell to get this information. So if it is currently set to 3, then it should sum(4,5,6,7) together. But if it changes to 2, then it should sum(4,5,6) together.

=DSUM(('CP'!A41)'CP'!AE250),"COL7",('DATA'!A124)'DATA'!D125))

But nothing happens and I am unsure as to why the criteria field is coming up with a #VALUE! when I have followed the example from MS help.

Thanks in advance.

 

[SkipVought]

Hi,

I think it's the way that you are defining your ranges.
[tt]
=DSUM(CP!A41:AE250,"COL7",DATA!A124125)
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[pmcmicha]

I modified the ranges as you have them specified here, but it did not have any effect.

 

[SkipVought]

It works PERFECTLY...

if your criteria is
[tt]
COL7 COL7
>0 <6
[/tt]
AND...
the database range INLCUDES YOUR COLUMN HEADINGS -- is the heading are in row 41

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[pmcmicha]

Yeah, I just figured that out. Thanks for the confirmation though.