Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
I am having trouble with a budget i have made.
I have created fromulas that are simple and just add up invoice amounts in a total row (simple addition sum(a1:13)), the problem is that when one of my staff need to enter a new invoice they insert a line right above the total line and it is not included in the formula as it is now located in a14. Is there a way to make the sum include all rows up to the formula cell? does this make sense |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
Is it at all possible to have the total above the rows....You will need to
insert a row. And enter a formula in the new A1 =sum(A2:A1000) 1000 rows enough? -- Greetings from New Zealand Bill K "Heather" wrote in message ... I am having trouble with a budget i have made. I have created fromulas that are simple and just add up invoice amounts in a total row (simple addition sum(a1:13)), the problem is that when one of my staff need to enter a new invoice they insert a line right above the total line and it is not included in the formula as it is now located in a14. Is there a way to make the sum include all rows up to the formula cell? does this make sense |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
Bill thanks for your suggestion, the only problem being that i have two
sub-categories in each column that need to be totalled separately (they are above one another) and the totalled completely, so it is impossible to fill in a set amount for each section, i was hoping for bit more flexible formula...maybe i am dreaming "Bill Kuunders" wrote: Is it at all possible to have the total above the rows....You will need to insert a row. And enter a formula in the new A1 =sum(A2:A1000) 1000 rows enough? -- Greetings from New Zealand Bill K "Heather" wrote in message ... I am having trouble with a budget i have made. I have created fromulas that are simple and just add up invoice amounts in a total row (simple addition sum(a1:13)), the problem is that when one of my staff need to enter a new invoice they insert a line right above the total line and it is not included in the formula as it is now located in a14. Is there a way to make the sum include all rows up to the formula cell? does this make sense |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
assuming the sum is located in a13, then name a13 as e.g. pos.
using this name, put a formula like =SUM(INDIRECT("a1:a"&ROW(pos)-1)) in a13. will this work for you? keizi "Heather" wrote in message ... I am having trouble with a budget i have made. I have created fromulas that are simple and just add up invoice amounts in a total row (simple addition sum(a1:13)), the problem is that when one of my staff need to enter a new invoice they insert a line right above the total line and it is not included in the formula as it is now located in a14. Is there a way to make the sum include all rows up to the formula cell? does this make sense |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
Hi Heather,
It sounds like you need a dynamic formula, so when rows are inserted above the totals row it includes them in the calculation. If this is what you're after then follow these steps. The following example is for a sheet named "Sheet1", with a formula in "A14" to sum "A1:A13": 1. Select cell "A14" 2. Define a local name as "'Sheet1'!LastCell" 3. Highlight the contents of the RefersTo box, and select cell "A13" 4. Remove the "$" sign from the address. Doing this makes the named cell relative to the cell containing the formula as one row above. As rows are inserted above the totals row, they will be included in the calculation. You must revise your formula as follows: =SUM(A1:LastCell) Good Luck! GS "Heather" wrote: I am having trouble with a budget i have made. I have created fromulas that are simple and just add up invoice amounts in a total row (simple addition sum(a1:13)), the problem is that when one of my staff need to enter a new invoice they insert a line right above the total line and it is not included in the formula as it is now located in a14. Is there a way to make the sum include all rows up to the formula cell? does this make sense |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a column up to a point
"kounoike" wrote in message
... assuming the sum is located in a13, then name a13 as e.g. pos. using this name, put a formula like =SUM(INDIRECT("a1:a"&ROW(pos)-1)) in a13. above is redundant, no need to use name, just =SUM(INDIRECT("a1:a"&ROW()-1)) would work for me. keizi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting every other data point in a column | Excel Worksheet Functions | |||
Complex Summing probably using Match at some point... | Excel Worksheet Functions | |||
summing column c based an column a criteria | Excel Worksheet Functions | |||
Stacked column chart wit more than one column in a data point | Charts and Charting in Excel | |||
Summing one column based on date in another column | New Users to Excel |