DSUM - Lotus vs Excel

  • Thread starter Thread starter Ken
  • Start date Start date
K

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.
 
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
Click to expand...

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
formula @DSUM(DATA said:
Please let me know if a statement like this is valid in
excel.
Click to expand...

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]
 
Back
Top