lotus 123 worksheet function conversion
thanks a lot for your help
"Harlan Grove" wrote:
andresg1975 wrote...
i have a worksheet like this:
column c d e f
row 3 date number amount
row 4 08/31/2006 13610 294.96 @dsum($data,"amount",number=d4)
....
Short answer, use
=SUMPRODUCT(--(INDEX(Data,0,2)=D4),INDEX(Data,0,3))
hardcoding the field numbers, or more generally
=SUMPRODUCT(--(INDEX(Data,0,MATCH("number",INDEX(Data,1,0),0))=D 4),
INDEX(Data,0,MATCH("amount",INDEX(Data,1,0),0)))
Excel's DSUM etc. functions are poor copies of 123 Release 2's
corresponding functions, i.e., they require the 3rd argument to be a
reference to a criteria range. Excel doesn't support criteria
expressions. On the other hand, you could try Excel's SQL.REQUEST
add-in function which allows you to use SQL queries, which are much
more powerful than 123's criteria expressions.
|