I have a spreadsheet with transactions with column headings
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:
-
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.
-
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?