Thanks:  0
Likes:  0

1. I have a table (labeled "Corp") \$A\$3:\$J\$196.

Column A is City
Column B is Company
Columns C-I are spending subtotals
Column J is the sum of Columns C-I

I am trying to set up a DSUM to show the spending for a company across the various cities.

In cell M6 I have put this formula:

=DSUM(Corp,J3:J196,CritRange)

where CritRange is a named range (\$M\$3) in which I type the company name.

However, no matter what I enter into M3 I receive the notice:

"#Value!"

What am I doing wrong?

Any help would be much appreciated.

_________________
- old, slow, and confused
... but at least I'm inconsistent -

[ This Message was edited by: shades on 2002-04-10 12:53 ]

2. On 2002-04-10 12:52, shades wrote:
I have a table (labeled "Corp") \$A\$3:\$J\$196.

Column A is City
Column B is Company
Columns C-I are spending subtotals
Column J is the sum of Columns C-I

I am trying to set up a DSUM to show the spending for a company across the various cities.

In cell M6 I have put this formula:

=DSUM(Corp,J3:J196,CritRange)

where CritRange is a named range (\$M\$3) in which I type the company name.

However, no matter what I enter into M3 I receive the notice:

"#Value!"

What am I doing wrong?

Any help would be much appreciated.

_________________
- old, slow, and confused
... but at least I'm inconsistent -

[ This Message was edited by: shades on 2002-04-10 12:53 ]
In M2 enter: City
In M3 enter: a city for which you want to compute a total/sum.

Use:

=DSUM(Corp,10,M2:M3)

where 10 is the column number of J.

3. Thanks. That works great!

Now I wonder why The "Using MS Excel 2000 SE" reference book doesn't give it this way?

So, with any of the D-Functions, is it always the column number that should be referenced in the "Field" rather than the name of the column?

4. On 2002-04-10 13:10, shades wrote:
Thanks. That works great!

Now I wonder why The "Using MS Excel 2000 SE" reference book doesn't give it this way?

So, with any of the D-Functions, is it always the column number that should be referenced in the "Field" rather than the name of the column?
The second arg can also be specified by the label of the column of interest within the double quotes instead of the column number of interest. The criteria range must also have the relevant labels for which you set up a criterion/condition, otherwise the D-function would fail.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•