DSUM Error

Posted on 2011-10-10
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.
Question by:fitaliano
    LVL 24

    Accepted Solution

    Not sure why, but you have to type this in your criteria section:

    LVL 33

    Expert Comment

    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.

    LVL 85

    Expert Comment

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


    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.

    LVL 33

    Expert Comment

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

    Expert Comment

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

    Expert Comment

    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.:)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now