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
6
Medium Priority
?
322 Views
Last Modified: 2012-05-12
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
Comment
Question by:fitaliano
  • 3
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
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

by:Norie
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

by:Rory Archibald
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

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

Expert Comment

by:Rory Archibald
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

by:Norie
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question