# Using named ranges to simplify formulae [closed]

Date, Item, Amount, Who

Date and amount are I hope obvious, Item is just a text description, and Who is somebodies initials.

I want to sum who has paid what so I can use sumif(Who column, person, Amount column) for example =SUMIF(Purchases.D2:D17,"JC",Purchases.C2:C17).

Two things I would like to do but can't see how:

1) Name the list of transactions e.g. purchases, then for the sumif somehow put in the formula "column 4 of purchases" i.e. put a subset of a named range in the fomula. Of course I can simulate this by naming ranges something like 'purchases_date, purchases_item etc., but it is not quite as good.

2) Have the named range extend when I add a row. At the moment my range does not include a header row (just labelling the columns) and a trailer row which provides the total expenditure. I want to be able to insert a row at the bottom and have the named range grow. At the moment I have to insert above the last line to have the range grow.

Item 2 is the most important. I need to allow the range to grow and the defined name to grow with it, so my calculations on the table (on another sheet) don't miss rows. How do others achieve this?

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-07 03:53:08.653354

Sort by » oldest newest most voted

First, I think could be good try with a Pivot table (Menu/Data/Pivot Table), much easy than compose your own formulas.

There is an option to use column labels in formulas (Menu/Insert/Names/Labels)

Also there is an option in: Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.

more