DSUM - Lotus vs Excel

[Ken]

In Lotus 123 I was able to attach an if statement to a
DSUM statement for the criteria. When I do this in Excel I
get #Name error. See Example:

A B C D
1 class TYPE AMT1 AMT2
2 w x y z
3 a e 100 200
4 b x 300 400
5 c e 500 600

Range name DATA = A2 : D5

Statement trying to use:

=DSUM(data,"y",IF(w="a",x="e",0))

Answer should be 600

Please let me know if a statement like this is valid in
excel.

 

[Bob Umlas]

DSUM formula's 3rd parameter MUST be a range reference, nothing else.

 

[Harlan Grove]

In Lotus 123 I was able to attach an if statement to a
DSUM statement for the criteria. When I do this in Excel I
get #Name error. See Example:

A B C D
1 class TYPE AMT1 AMT2
2 w x y z
3 a e 100 200
4 b x 300 400
5 c e 500 600

Range name DATA = A2 : D5

Statement trying to use:

=DSUM(data,"y",IF(w="a",x="e",0))

Answer should be 600

No it shouldn't. You're using w and x as field names, and only the record in row
3 satisfies the w="a" criterion, so in 123 (just tested in 123 Release 5) the
formula @DSUM(DATA,"y",@IF(W="a",X="e",0)) returns 100. On the other hand, the

Please let me know if a statement like this is valid in
excel.

Nope. Excel supports 123 Release 2.01 @DSUM functionality, no more. Meaning that
Excel's DSUM requires a reference to a criteria *RANGE* as 3rd argument.

There are work-arounds of varying degrees of complexity. See the following
archived thread.

http://www.google.com/[email protected]