DSUM vs SUMIF

[Guest]

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.

 

[JulieD]

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

 

[Guest]

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?

 

[Aladin Akyurek]

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

 

[Bob Tarbuton]

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.

 

[Aladin Akyurek]

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.

 

[Harlan Grove]

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...?OpenView&ExpandView&RestricttoCategory=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.

 

[Guest]

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