asked on

DSUM Error

In the following file the DSUM is picking up more than it should... It seems to be making a sort of "like" when summing up the results. If you open the file you can clearly see what I mean.

I want the DSUM to pick up exactly what specified in the Criteria.
MS-DSUM-Error.xls

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

I've seen the same problem but never found an explanation.

A lot of people just suggest replacing the DSUM with another function, eg COUNTIF, SUMPRODUCT.

For example in this case:

=SUMPRODUCT(--($B$4:$B$8=K4), --($C$4:$C$8=L4), $D$4:$D$8)

Or in later versions (2007-) SUMIFS.

=SUMIFS(D4:D8,B4:B8,K4,C4:C8,L4)

Rory Archibald

Per Excel Help in the criteria examples:

To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:
=''=entry''

and:

To find text values that share some characters but not others, do one or more of the following:

Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."

It works the same way as the Advanced Filter.

HTH
Rory

Norie

Help - about the only place I didn't look.

Rory Archibald

To be fair, I was looking at 2003's help as I gave up on it from 2007 onwards. :)

Norie

Actually I did find it in 2010 help just now.

Embarassingly I also found it last night here.

Just didn't read it through properly.:)