Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# DSUM Error

Posted on 2011-10-10
Medium Priority
322 Views
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
0
Question by:fitaliano
• 3
• 2

LVL 24

Accepted Solution

StephenJR earned 2000 total points
ID: 36945531
Not sure why, but you have to type this in your criteria section:

="=ABS"
0

LVL 36

Expert Comment

ID: 36945666
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)
0

LVL 85

Expert Comment

ID: 36947085
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
0

LVL 36

Expert Comment

ID: 36947878
Help - about the only place I didn't look.
0

LVL 85

Expert Comment

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

LVL 36

Expert Comment

ID: 36948019
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.:)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month13 days, 9 hours left to enroll