MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Error with Dsum


Posted by Bob Powell on February 23, 2001 12:13 PM

Dsum fuction returns incorrect answer vs. criteria
in Excel 97 SR 2
__________________________________
Database Criteria
Field 1 Field 2 Field 1
a 1 a
ab 1
b 1
abc 1

=dsum(database,Field2,Criteria) returns 3 NOT 1

Have I missed a a wildcard issue in using the dfunctions?


Posted by Dave Hawley on February 23, 2001 12:43 PM


Bob, I see what you mean!

Perhaps you can use the SUMIF instead:
=SUMIF(A1:B5,"a",B1:B5)


Dave


OzGrid Business Applications

Posted by Mark W. on February 23, 2001 1:17 PM

Nothing is wrong with DSUM() -- it's your criteria.
Excel will treat "a" as though it was "a*". I
recommend that you use a single column computed
criteria. Leave the 1st row of the criteria range
blank and enter, =EXACT("a",A2), into the
the cell on the 2nd row where A2 is the 1st cell
containing data in your Field 1 column.

Posted by Mark W. on February 23, 2001 1:45 PM

If you don't want to use a computed criteria...

If you don't want to use a computed criteria you
can use ="=a" instead of "a".