function taken from excel doesn't work on Calc

[rookie]

Hello to the forum.

I'm trying to migrate a spreadsheet from MS Excel to OO.o Calc but the function that I use in one cell doesn't work.

Here is the function in the 'K11':

=IF(F11=0;0;IF(F11=1;L12;SUM(L12:OFFSET(K11;F11;1))))

'F11' should be 0 or a positive integer.

For 'F11'=0 I get 0.
For 'F11'=2 I get 1.
Now, if 'F11' is anything else I should get the sum of the cells starting from 'L12' (that is one line below the cell hosting the function and i column to the right) to down as many cells as the integer in 'F11' indicates.

The mentioned function works in Excel but is Calc I get an error about invalid name. If I change the function to:

=IF(F11=0;0;IF(F11=1;L12;SUM(L12;OFFSET(K11;F11;1))))

it works fine. I.e. it only returns the sum of 'L12' and the cell 'F11' lines below. Any ideas why the ':' in the SUM function doesn't work in this case? If not, any other ways to achieve what this functions does under Excel in Calc?

Thank you in advance...

[RickRandom]

Can you use the OFFSET() function to return the whole range you want, not just the last cell?

Something like

=IF(F11=0;0;IF(F11=1;L12;SUM(OFFSET(L12;0;0;F11;2))))

but I haven't tried it

[rookie]

Thank you for your reply!

I've tried your suggestion but now I get an error about "circular reference".

In the way you use OFFSET(), you specify the number of rows that you moved down but you specify the total column number in the moved area, correct?

[RickRandom]

[RickRandom]

[rookie]

The "circular reference" error was my mistake because I didn't change the reference cell from 'K11' to 'L12' in the OFFSET(). So I do apologize for making you spent more time on my problem!