Ask Your Question
0

can I use a cell reference in the sum command? [closed]

asked 2014-05-02 20:15:27 +0200

BenG gravatar image

I have a spread sheet where I enter the dates and amount of quotations sent out. A set of columns to the right of those is used to sum up the quotes for different periods of time. There is a 30 day column, a 60 day column.....

Whenever I add a new quote (date, amount), I have to manually extend the range of every sum function. If I have entries down to row 50, I have to go back to each Sum(c1:c45) and change c45 to c50.

I would like to be able to put "50", or "c50" (or anything) in a cell that would be referenced in each sum command to extend the column to the inserted reference. Say I put "c50" in Cell f2. I would like to have every sum look something like sum(c1:"f2"), where it would read c50 and extend the sum.

Is something like this possible?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 20:04:21.316420

2 Answers

Sort by » oldest newest most voted
0

answered 2014-05-03 01:49:42 +0200

m.a.riosv gravatar image

Well, I think you have a better alternative, activating the option:
Menu/Tools/Options/LIbreOffice calc/General/Input settings - Expand references when new columns/rows are inserted.

Then inserting a row just below the last cell with data do the job.

Also there are other ways to know what is the last row with data.

=SUMPRODUCT(MAX(ROW(A2:A20)*NOT(ISBLANK(A2:A20))))
find the last row with data in A2:A20

edit flag offensive delete link more
0

answered 2014-05-02 20:31:48 +0200

BenG gravatar image

Well, I went on the web elsewhere and figured it out.

The command is sum(C1:indirect(f2)). In f2 put C50. everything works.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-05-02 20:15:27 +0200

Seen: 218 times

Last updated: May 03 '14