Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted

[sga]

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.

 

[Ron Rosenfeld]

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.

 

[Pete_UK]

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

 

[sga]

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.