dsum returning wrong value
Markus Janscha
using MSQuery. I then sum up (using dsum) the transactions of several
codes, which are 2-3 letter codes (FB, CD, ABC,etc).
But I have one code called "CN" and another one called "CND". The dsum
criteria looking for "CN" always picks up "CND" too.
Is this a bug? IT seems that dsum stops evaluating a string after the
criteria length has been met and the string matches over the criteria
length.
Is there any way to bypass this, as it seriously affects my results. Soon a
range of new 3 letter codes will be added to the database which means I will
no longer be able to use Excel for analysis.
Thanks
Markus
Dick Kusleika
experiences are identical to what I've experienced. A couple of people in
this newsgroup told me not to use DSUM but to use an array formula instead.
Since I've learned them, I haven't used any Database functions (not that
they don't have merit).
In general if your codes are in column A and your values to sum are in
column B, then an array formula might look like this:
=SUM((A1:A100="CND")*(B1:B100)) enter with control+shift+enter
If you enter it properly, XL will put curly braces up in the formula bar -
don't enter the braces yourself (experience talking)
Also, see Chip Pearson's great site on array's (and other topics) at
www.cpearson.com
HTH
Dick K.
Markus Janscha <mar...@nospam.janscha.co.uk> wrote in message
news:8nel6d$qom$1...@news5.svr.pol.co.uk...
Myrna Larson
<mar...@nospam.janscha.co.uk> wrote:
This isn't a bug. Criteria ranges have always worked that way. It is
documented in Help, under the topic "Types of Comparison Criteria" (XL2000).
Here's the info from Help:
~~~~~~~~~~~~~~~~~~~~~~~~~~~
To find rows in a list that contain an exact value, type the text, number,
date, or logical value in the cell below the criteria label. For example, if
you type 98133 below a Postal Code label in the criteria range, Microsoft
Excel displays only rows that contain the postal code value "98133."
When you use text as criteria with an advanced filter, Microsoft Excel finds
all items that begin with that text. For example, if you type the text Dav as
a criterion, Microsoft Excel finds "Davolio," "David," and "Davis." To match
only the specified text, type the following formula, where text is the text
you want to find.
="=text"
~~~~~~~~~~~~~~~~~~~~~~~~~~~
In your case, you would use ="=CD"
You could also omit the field name from the top row and in the row below use
the formula
=A2="CD"
where A2 is a relative reference to the appropriate column in the first record
of the database.
Bob Umlas
To get only CN without CND, have your criteria cell be this(exactly):
="=CN"
Then the DSUM which refers to this will NOT get the CND.
Bob Umlas
Excel MVP
Markus Janscha <mar...@nospam.janscha.co.uk> wrote in message
news:8nel6d$qom$1...@news5.svr.pol.co.uk...
Markus Janscha
I managed to change the formulae. The array solution works v. well.
Nevertheless I feel that the dsum etc functions could do with and optional
parameter for finding exact matches only.
Markus
Dick Kusleika <par...@radiksns.net> wrote in message
news:W_Am5.27494$B86.8...@nntp2.onemain.com...
> I don't think it's technically a bug, but it sure is inconvenient. Your
> experiences are identical to what I've experienced. A couple of people in
> this newsgroup told me not to use DSUM but to use an array formula
instead.
> Since I've learned them, I haven't used any Database functions (not that
> they don't have merit).
>
> In general if your codes are in column A and your values to sum are in
> column B, then an array formula might look like this:
>
> =SUM((A1:A100="CND")*(B1:B100)) enter with control+shift+enter
>
> If you enter it properly, XL will put curly braces up in the formula bar -
> don't enter the braces yourself (experience talking)
>
> Also, see Chip Pearson's great site on array's (and other topics) at
> www.cpearson.com
>
> HTH
> Dick K.
>
Myrna Larson
<mar...@nospam.janscha.co.uk> wrote:
>I managed to change the formulae. The array solution works v. well.
>Nevertheless I feel that the dsum etc functions could do with and optional
>parameter for finding exact matches only.
Did you see my previous reply?
The way to find only exact matches is to write the criterion as
="=CND"
or
="=CN"
Include the quote marks as I show them.
This is documented in Help if you search for Comparison Criteria. The topic
name is "Types of Comparison Criteria". (XL2000, but I think it's the same in
earlier versions.)