Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted
Hello - I am using Excel 2000 and wonder if anyone has any suggestion
for the below problem - I have an Excel sheet where Column A shows a client name and Columns C and D have a $ amount and Column E has an employee name. There can be multiple employees and $ amounts per client (or no $ amounts). Under the Client name I have the sum of the $ amounts per client. But there are rows added and deleted as well as $ amounts. So how can I use a dynamic sum function to only sum the rows and columns until it reaches the next client (A5 is not null)? Currently I manually adjust each hardcoded Sum. But I thought about trying some sort of sum(offset) - I tried =SUM(OFFSET(A2, -1, 2, 3,2)) =SUM(OFFSET(A6, -1, 2, 2,2)) =SUM(OFFSET(A10, -1, 2, 1,2)) Which gives me the correct sum values. But in my case I need a way for the last 2 arguments (height and width) to be dynamic - actually the width is fixed so really dynamic height since I am adding rows not columns. Example: Client1 $400 $100 Employee 1 $2000 $500 Employee 2 $600 $400 Employee 5 Client2 $300 Employee 3 $750 $400 $50 Employee 4 Client3 $200 $50 Employee 6 $250 Thanks for any suggestion. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000 formula to dynamically sum adjacent rows and columns where rows inserted/deleted
On Mon, 11 Apr 2011 19:29:58 -0700 (PDT), sga wrote:
Hello - I am using Excel 2000 and wonder if anyone has any suggestion for the below problem - I have an Excel sheet where Column A shows a client name and Columns C and D have a $ amount and Column E has an employee name. There can be multiple employees and $ amounts per client (or no $ amounts). Under the Client name I have the sum of the $ amounts per client. But there are rows added and deleted as well as $ amounts. So how can I use a dynamic sum function to only sum the rows and columns until it reaches the next client (A5 is not null)? Currently I manually adjust each hardcoded Sum. But I thought about trying some sort of sum(offset) - I tried =SUM(OFFSET(A2, -1, 2, 3,2)) =SUM(OFFSET(A6, -1, 2, 2,2)) =SUM(OFFSET(A10, -1, 2, 1,2)) Which gives me the correct sum values. But in my case I need a way for the last 2 arguments (height and width) to be dynamic - actually the width is fixed so really dynamic height since I am adding rows not columns. Example: Client1 $400 $100 Employee 1 $2000 $500 Employee 2 $600 $400 Employee 5 Client2 $300 Employee 3 $750 $400 $50 Employee 4 Client3 $200 $50 Employee 6 $250 Thanks for any suggestion. Word wrapping messed up your table in my reader so I can't provided specifics. How do you tell where the data from one client starts and the next ends? Sometimes, a simple SUM formula can be set up and will adjust for row insertions/deletions; especially if there is an empty cell at the top/bottom that can be used for the ends of the range. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000 formula to dynamically sum adjacent rows and columnswhere rows inserted/deleted
If you had the client name on every row in column A then you could use
SUMIF. A quick way to accomplish this is to highlight column A for the full extent of data in column B, starting with the first blank cell in column A. Press F5 (GoTo), then click Special and then Blanks. Then begin to type a formula by entering = and then click on the cell above (i.e. the cell containing Client1), and then do CTRL-Enter. This will fill all the blanks with the client names (it doesn't matter about the blank rows between clients). Then with a list of clients (maybe in column A on a separate sheet), you just need this formula in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:C) and copy it down. Hope this helps. Pete On Apr 12, 3:29*am, sga wrote: Hello - I am using Excel 2000 and wonder if anyone has any suggestion for the below problem - I have an Excel sheet where Column A shows a client name and Columns C and D have a $ amount and Column E has an employee name. There can be multiple employees and $ amounts per client (or no $ amounts). *Under the Client name I have the sum of the $ amounts per client. But there are rows added and deleted as well as $ amounts. *So how can I use a dynamic sum function to only sum the rows and columns until it reaches the next client (A5 is not null)? *Currently I manually adjust each hardcoded Sum. *But I thought about trying some sort of sum(offset) - I tried =SUM(OFFSET(A2, -1, 2, 3,2)) =SUM(OFFSET(A6, -1, 2, 2,2)) =SUM(OFFSET(A10, -1, 2, 1,2)) Which gives me the correct sum values. *But in my case I need a way for the last 2 arguments (height and width) to be dynamic - actually the width is fixed so really dynamic height since I am adding rows not columns. Example: Client1 * $400 $100 * Employee 1 $2000 * *$500 * * * * * Employee 2 * * * * * * *$600 $400 * Employee 5 Client2 * $300 * * * * * Employee 3 $750 * * *$400 *$50 * *Employee 4 Client3 * $200 $50 * * Employee 6 $250 Thanks for any suggestion. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000 formula to dynamically sum adjacent rows and columnswhere rows inserted/deleted
For word wrap problem - Column A has the Client name and then it is
blank until a new Client name in Column A. In the example A1 = Client1, C1 = $400, D1 = $100, E1 = Employee1, A2 = the sum $2000, C2 = $500, D2 = blank, E2 = Employee2, etc. The client name is always in Column A with the sum immediately below it and the amounts are adjacent to A. Thanks for the suggestion about putting the client name in each row. The sheet is about 1000 rows with 100 different client names. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
How can I protect a sheet so the rows/columns can't be deleted? | Excel Worksheet Functions | |||
Change colour of cells when content is altered/changed BUT NOT TO INSERTED OR DELETED ROWS | Excel Discussion (Misc queries) | |||
Transpose: many columns to inserted rows | Excel Discussion (Misc queries) | |||
If a Row is inserted or is a row is deleted then...... | Excel Worksheet Functions |