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.