DSUM vs SUMIF

I have read several messages in this group about the problem with DSUM and
matching the criteria values in that a criteria of "ABC" also matches
"ABC123".

Based on suggestions in the group, I tried using the SUMIF statements. But,
lo and behold, the same problem surfaces! Apparantly, Excel just plain
cannot tell the difference in values.

I agree with some other postings that the Lotus functions are MUCH more
flexible and useful. In fact, the ONLY reason I am attempting Excel is
because of a company standard.

Because of the volume of work we do that requires accurate criteria
matching, I would definitely use Lotus over Excel. This problem makes Excel
useless for us. If I could, I would recommend that our company use Lotus.

Hi Martin

(leaving aside the whole lotus vs excel question)

if i have the following values
........A.............B
1....ABC123...5
2....ABC.........5

and use the formula
=SUMIF(A1:A2,"ABC",B1:B2)
i end up with the answer of 5

so i'm not sure what you mean in your post about SUMIF matching ABC and
ABC123?

Cheers
JulieD


"Martin" <[email protected]> wrote in message
news:[email protected]...
>I have read several messages in this group about the problem with DSUM and
> matching the criteria values in that a criteria of "ABC" also matches
> "ABC123".
>
> Based on suggestions in the group, I tried using the SUMIF statements.
> But,
> lo and behold, the same problem surfaces! Apparantly, Excel just plain
> cannot tell the difference in values.
>
> I agree with some other postings that the Lotus functions are MUCH more
> flexible and useful. In fact, the ONLY reason I am attempting Excel is
> because of a company standard.
>
> Because of the volume of work we do that requires accurate criteria
> matching, I would definitely use Lotus over Excel. This problem makes
> Excel
> useless for us. If I could, I would recommend that our company use Lotus.
>
>
>

I think the target is DSUM() and the observation is right, but easily
avoided for example with...

=EXACT(A2,"ABC")

where A2 is the first data cell to match against (while A1 houses a header).

JulieD wrote:
> Hi Martin
>
> (leaving aside the whole lotus vs excel question)
>
> if i have the following values
> .......A.............B
> 1....ABC123...5
> 2....ABC.........5
>
> and use the formula
> =SUMIF(A1:A2,"ABC",B1:B2)
> i end up with the answer of 5
>
> so i'm not sure what you mean in your post about SUMIF matching ABC and
> ABC123?
>
> Cheers
> JulieD
>
>
> "Martin" <[email protected]> wrote in message
> news:[email protected]...
>
>>I have read several messages in this group about the problem with DSUM and
>>matching the criteria values in that a criteria of "ABC" also matches
>>"ABC123".
>>
>>Based on suggestions in the group, I tried using the SUMIF statements.
>>But,
>>lo and behold, the same problem surfaces! Apparantly, Excel just plain
>>cannot tell the difference in values.
>>
>>I agree with some other postings that the Lotus functions are MUCH more
>>flexible and useful. In fact, the ONLY reason I am attempting Excel is
>>because of a company standard.
>>
>>Because of the volume of work we do that requires accurate criteria
>>matching, I would definitely use Lotus over Excel. This problem makes
>>Excel
>>useless for us. If I could, I would recommend that our company use Lotus.
>>
>>
>>
>
>
>

Interesting stuff, but if Martin is disappointed in the
flexibility of Excel shouldn't he be introduced to
sumproduct as an alternative to sumif?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Also, Aladin and JulieD, in what situation would recommend
using DSUM? I don't mean to be wise, just curious if there
is any.

>-----Original Message-----
>I think the target is DSUM() and the observation is
right, but easily
>avoided for example with...
>
>=EXACT(A2,"ABC")
>
>where A2 is the first data cell to match against (while
A1 houses a header).
>
>JulieD wrote:
>> Hi Martin
>>
>> (leaving aside the whole lotus vs excel question)
>>
>> if i have the following values
>> .......A.............B
>> 1....ABC123...5
>> 2....ABC.........5
>>
>> and use the formula
>> =SUMIF(A1:A2,"ABC",B1:B2)
>> i end up with the answer of 5
>>
>> so i'm not sure what you mean in your post about SUMIF
matching ABC and
>> ABC123?
>>
>> Cheers
>> JulieD
>>
>>
>> "Martin" <[email protected]> wrote in
message
>> news:CF8766EE-DAC3-4B7E-B498-
[email protected]...
>>
>>>I have read several messages in this group about the
problem with DSUM and
>>>matching the criteria values in that a criteria
of "ABC" also matches
>>>"ABC123".
>>>
>>>Based on suggestions in the group, I tried using the
SUMIF statements.
>>>But,
>>>lo and behold, the same problem surfaces! Apparantly,
Excel just plain
>>>cannot tell the difference in values.
>>>
>>>I agree with some other postings that the Lotus
functions are MUCH more
>>>flexible and useful. In fact, the ONLY reason I am
attempting Excel is
>>>because of a company standard.
>>>
>>>Because of the volume of work we do that requires
accurate criteria
>>>matching, I would definitely use Lotus over Excel.
This problem makes
>>>Excel
>>>useless for us. If I could, I would recommend that our
company use Lotus.
>>>
>>>
>>>
>>
>>
>>
>.
>

Bob Tarbuton wrote:
> Interesting stuff, but if Martin is disappointed in the
> flexibility of Excel shouldn't he be introduced to
> sumproduct as an alternative to sumif?
[...]

If the totals one wants to calculate is based on a single condition (or
a between condition like X and Y where X and Y are numeric), SumIf is
best choice. DSUM() can be invoked for conditional summing as long as
one works with Data Tables or use set ups that allow copying down a DSUM
formula. Speedwise, it can be a good alternative to formulas that
operate on computed arrays. Alas, the set up that DSUM() requires is not
that hassle-free.

Having used both over the years, I've come to the conclusion that both have
strong and weak points, and both do the job, more or less. Actually, yours
is a rather restrained posting - you should read how much people rag on 123
over in the Lotus news groups....Does that say more about the manners of
people who prefer Lotus or the zealousness of people who prefer Excel?

"Martin" wrote:

> I have read several messages in this group about the problem with DSUM and
> matching the criteria values in that a criteria of "ABC" also matches
> "ABC123".
>
> Based on suggestions in the group, I tried using the SUMIF statements. But,
> lo and behold, the same problem surfaces! Apparantly, Excel just plain
> cannot tell the difference in values.
>
> I agree with some other postings that the Lotus functions are MUCH more
> flexible and useful. In fact, the ONLY reason I am attempting Excel is
> because of a company standard.
>
> Because of the volume of work we do that requires accurate criteria
> matching, I would definitely use Lotus over Excel. This problem makes Excel
> useless for us. If I could, I would recommend that our company use Lotus.
>
>
>

Tom Hayakawa wrote...
>Having used both over the years, I've come to the conclusion that both
have
>strong and weak points, and both do the job, more or less. Actually,
yours
>is a rather restrained posting - you should read how much people rag
on 123
>over in the Lotus news groups....Does that say more about the manners
of
>people who prefer Lotus or the zealousness of people who prefer Excel?

....

What Lotus newsgroup? Ain't any Lotus 123 USENET-like newsgroups. Do
you mean comp.apps.spreadsheets? Or do you mean the Domino-hosted 123
community forum

http://www-10.lotus.com/ldd/ssforum....Category=1-2-3

?

In either case, provide 1 link to any rags or rants against 123 posted
in the last 12 months. Just try to find one. Show us you're not just
spewing BS.

Anyone who knows anything about *BOTH* Excel and 123 knows that when it
comes to 'database' functions, 123 remains vastly supoerior to Excel
because Lotus decided to make @DSUM and the like capable of really
useful functionality back in 123 Release 3.0, which came out in 1989
(more than 15 years ago). Microsoft, on the other hand, decided that
merely copying 123 Release 2.x functionality was sufficient. For a
while Microsoft did provide an even better approach with its
SQL.REQUEST add-in function, but they've apparently deprecated it in
favor of nothing (i.e., it no longer ships with Excel 2003, and there's
no alternative provided).

The only really useful features 123 lacks compared to Excel are array
formulas, the OFFSET function, custom number formatting and the Text to
Columns wizard in worksheets and a securable object model and a decent
IDE in scripting. Most of the time 123 is superior to Excel in terms of
formulas. And it's nearly impossible to confuse 123 so thoroughly that
it'd need several megabytes of disk storage for a handful of formatted
cells.

Harlan,

Actually, I was referring to the internal newsgroups in IBM and Lotus, which
I guess you haven't seen and which, unfortunately, I also no longer have
access to since I no longer work for IBM. It appears you are violently
agreeing with me - 123 does some things better, and Excel does some things
better. If it were possible to combine the good points of both, we'd have a
better spreadsheet app.

Pax,
Tom Hayakawa

"Harlan Grove" wrote:

> Tom Hayakawa wrote...
> >Having used both over the years, I've come to the conclusion that both
> have
> >strong and weak points, and both do the job, more or less. Actually,
> yours
> >is a rather restrained posting - you should read how much people rag
> on 123
> >over in the Lotus news groups....Does that say more about the manners
> of
> >people who prefer Lotus or the zealousness of people who prefer Excel?
>
> ....
>
> What Lotus newsgroup? Ain't any Lotus 123 USENET-like newsgroups. Do
> you mean comp.apps.spreadsheets? Or do you mean the Domino-hosted 123
> community forum
>
> http://www-10.lotus.com/ldd/ssforum....Category=1-2-3
>
> ?
>
> In either case, provide 1 link to any rags or rants against 123 posted
> in the last 12 months. Just try to find one. Show us you're not just
> spewing BS.
>
> Anyone who knows anything about *BOTH* Excel and 123 knows that when it
> comes to 'database' functions, 123 remains vastly supoerior to Excel
> because Lotus decided to make @DSUM and the like capable of really
> useful functionality back in 123 Release 3.0, which came out in 1989
> (more than 15 years ago). Microsoft, on the other hand, decided that
> merely copying 123 Release 2.x functionality was sufficient. For a
> while Microsoft did provide an even better approach with its
> SQL.REQUEST add-in function, but they've apparently deprecated it in
> favor of nothing (i.e., it no longer ships with Excel 2003, and there's
> no alternative provided).
>
> The only really useful features 123 lacks compared to Excel are array
> formulas, the OFFSET function, custom number formatting and the Text to
> Columns wizard in worksheets and a securable object model and a decent
> IDE in scripting. Most of the time 123 is superior to Excel in terms of
> formulas. And it's nearly impossible to confuse 123 so thoroughly that
> it'd need several megabytes of disk storage for a handful of formatted
> cells.
>
>