SUMIF with OFFSET

Hello

I am trying to find a way to use SUMIF with OFFSET on a range of variable
length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client has
at least one entry for Fee1 through Fee5. The number of rows for any
particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this example).
There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for Client
1. Since the number of rows for Client 1 will vary, I am trying to use the
MATCH function to identify the number of rows associated with Client 1 by
looking for the first blank cell in Column E at the bottom of the data for
each client.

The following formula gives a #REF! error. When I step through the formula,
the two OFFSET functions appear to show the correct inputs of OFFSET(E9, 0,
0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one individually gives the
#REF! error. The value in cell E9 is the text string "Fee1". The total of
Fee1 for Client 1 should be 20 in this example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0), E9,
OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0))

According to the Excel help, #REF! errors mean that the OFFSET goes beyond
the edges of the worksheet, but I don't think I am doing this. Any help
would be gratefully appreciated, either in fixing this formula or offering a
better solution. Whatever formula I use will need to be copied down for
several hundred clients. Thank you in advance!

How do you identify fees that are related to a specific client?

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Hello

I am trying to find a way to use SUMIF with OFFSET on a range of variable
length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client has
at least one entry for Fee1 through Fee5. The number of rows for any
particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this
example). There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for
Client 1. Since the number of rows for Client 1 will vary, I am trying to
use the MATCH function to identify the number of rows associated with
Client 1 by looking for the first blank cell in Column E at the bottom of
the data for each client.

The following formula gives a #REF! error. When I step through the
formula, the two OFFSET functions appear to show the correct inputs of
OFFSET(E9, 0, 0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one
individually gives the #REF! error. The value in cell E9 is the text
string "Fee1". The total of Fee1 for Client 1 should be 20 in this
example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0),
E9, OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0))

According to the Excel help, #REF! errors mean that the OFFSET goes beyond
the edges of the worksheet, but I don't think I am doing this. Any help
would be gratefully appreciated, either in fixing this formula or offering
a better solution. Whatever formula I use will need to be copied down for
several hundred clients. Thank you in advance!

Thanks for the fast reply, Biff!

In the formula I posted, the row reference in the formula will start with
the first row for the first client, Row 9 in this example. The number of
rows in the formula (row 9 - row 60) is intended to be well beyond the
number of rows any client would ever have so I should not need to ever
update the formulas for this reason. The last row associated with a given
client is identified by the blank row (Row 18 in this example). It was my
intention to start with row 9 and then look down to the first blank cell in
Column E to find the end of the data for the client.

I thought that when I copied this formula down to the other clients and
pasted it into the appropriate row for each client, the range of rows
associated with each client would be calculated automatically by the offset
function due to the relative referencing.

On my worksheet, the client names are in Column A, on the first row of the
data for that client. In my example, the name "Client 1" would be only in
cell A9.

I suppose one option could be to put in an extra hidden column that had the
client name in every row. Then I could use SUMPRODUCT to do a double test
for the client name and the fee name, and this SUMPRODUCT formula would be
easy to write. One disadvantage of this method would be the need to add the
client name to each new row as fees are added. I would prefer not to have
to remember to do this.

If this does not answer your question, please let me know what other
information I need to provide.

Thank you again very much!



"T. Valko" wrote in message
...
How do you identify fees that are related to a specific client?

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Hello

I am trying to find a way to use SUMIF with OFFSET on a range of variable
length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client
has at least one entry for Fee1 through Fee5. The number of rows for any
particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this
example). There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for
Client 1. Since the number of rows for Client 1 will vary, I am trying
to use the MATCH function to identify the number of rows associated with
Client 1 by looking for the first blank cell in Column E at the bottom of
the data for each client.

The following formula gives a #REF! error. When I step through the
formula, the two OFFSET functions appear to show the correct inputs of
OFFSET(E9, 0, 0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one
individually gives the #REF! error. The value in cell E9 is the text
string "Fee1". The total of Fee1 for Client 1 should be 20 in this
example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0),
E9, OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0))

According to the Excel help, #REF! errors mean that the OFFSET goes
beyond the edges of the worksheet, but I don't think I am doing this.
Any help would be gratefully appreciated, either in fixing this formula
or offering a better solution. Whatever formula I use will need to be
copied down for several hundred clients. Thank you in advance!

I suppose one option could be to put in an extra hidden
column that had the client name in every row.
I would prefer not to have to remember to do this.

Well, you might want to reconsider that after you see this formula!

Let's assume you want to sum Fee1 for a specific client.

Client names in column A starting in cell A9.
Fee category in column E starting in cell E9
Fee amounts in column F starting in cell F9.

There *must* be an empty cell at the end of the range in column E.

H9 = the client to sum for like Client1
I9 = fee category like Fee1

=SUMPRODUCT(--(OFFSET(E9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0))=I9),OFFSET(F9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0)))

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Thanks for the fast reply, Biff!

In the formula I posted, the row reference in the formula will start with
the first row for the first client, Row 9 in this example. The number of
rows in the formula (row 9 - row 60) is intended to be well beyond the
number of rows any client would ever have so I should not need to ever
update the formulas for this reason. The last row associated with a given
client is identified by the blank row (Row 18 in this example). It was my
intention to start with row 9 and then look down to the first blank cell
in Column E to find the end of the data for the client.

I thought that when I copied this formula down to the other clients and
pasted it into the appropriate row for each client, the range of rows
associated with each client would be calculated automatically by the
offset function due to the relative referencing.

On my worksheet, the client names are in Column A, on the first row of the
data for that client. In my example, the name "Client 1" would be only in
cell A9.

I suppose one option could be to put in an extra hidden column that had
the client name in every row. Then I could use SUMPRODUCT to do a double
test for the client name and the fee name, and this SUMPRODUCT formula
would be easy to write. One disadvantage of this method would be the need
to add the client name to each new row as fees are added. I would prefer
not to have to remember to do this.

If this does not answer your question, please let me know what other
information I need to provide.

Thank you again very much!



"T. Valko" wrote in message
...
How do you identify fees that are related to a specific client?

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Hello

I am trying to find a way to use SUMIF with OFFSET on a range of
variable length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client
has at least one entry for Fee1 through Fee5. The number of rows for
any particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this
example). There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for
Client 1. Since the number of rows for Client 1 will vary, I am trying
to use the MATCH function to identify the number of rows associated with
Client 1 by looking for the first blank cell in Column E at the bottom
of the data for each client.

The following formula gives a #REF! error. When I step through the
formula, the two OFFSET functions appear to show the correct inputs of
OFFSET(E9, 0, 0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one
individually gives the #REF! error. The value in cell E9 is the text
string "Fee1". The total of Fee1 for Client 1 should be 20 in this
example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0),
E9, OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0))

According to the Excel help, #REF! errors mean that the OFFSET goes
beyond the edges of the worksheet, but I don't think I am doing this.
Any help would be gratefully appreciated, either in fixing this formula
or offering a better solution. Whatever formula I use will need to be
copied down for several hundred clients. Thank you in advance!

Thank you very much, Biff. Your formula works. I do greatly appreciate
your time, and the time all of the contributors to this forum. You all do
great work!

I also figured out the error in my original formula. I was using zero as the
width parameter in the two offset functions, but obviously both the width
and height parameters must be at least 1. It was not so obvious to me last
night.



"T. Valko" wrote in message
...
I suppose one option could be to put in an extra hidden
column that had the client name in every row.
I would prefer not to have to remember to do this.

Well, you might want to reconsider that after you see this formula!

Let's assume you want to sum Fee1 for a specific client.

Client names in column A starting in cell A9.
Fee category in column E starting in cell E9
Fee amounts in column F starting in cell F9.

There *must* be an empty cell at the end of the range in column E.

H9 = the client to sum for like Client1
I9 = fee category like Fee1

=SUMPRODUCT(--(OFFSET(E9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0))=I9),OFFSET(F9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0)))

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Thanks for the fast reply, Biff!

In the formula I posted, the row reference in the formula will start with
the first row for the first client, Row 9 in this example. The number of
rows in the formula (row 9 - row 60) is intended to be well beyond the
number of rows any client would ever have so I should not need to ever
update the formulas for this reason. The last row associated with a
given client is identified by the blank row (Row 18 in this example). It
was my intention to start with row 9 and then look down to the first
blank cell in Column E to find the end of the data for the client.

I thought that when I copied this formula down to the other clients and
pasted it into the appropriate row for each client, the range of rows
associated with each client would be calculated automatically by the
offset function due to the relative referencing.

On my worksheet, the client names are in Column A, on the first row of
the data for that client. In my example, the name "Client 1" would be
only in cell A9.

I suppose one option could be to put in an extra hidden column that had
the client name in every row. Then I could use SUMPRODUCT to do a double
test for the client name and the fee name, and this SUMPRODUCT formula
would be easy to write. One disadvantage of this method would be the
need to add the client name to each new row as fees are added. I would
prefer not to have to remember to do this.

If this does not answer your question, please let me know what other
information I need to provide.

Thank you again very much!



"T. Valko" wrote in message
...
How do you identify fees that are related to a specific client?

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Hello

I am trying to find a way to use SUMIF with OFFSET on a range of
variable length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client
has at least one entry for Fee1 through Fee5. The number of rows for
any particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this
example). There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for
Client 1. Since the number of rows for Client 1 will vary, I am trying
to use the MATCH function to identify the number of rows associated
with Client 1 by looking for the first blank cell in Column E at the
bottom of the data for each client.

The following formula gives a #REF! error. When I step through the
formula, the two OFFSET functions appear to show the correct inputs of
OFFSET(E9, 0, 0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one
individually gives the #REF! error. The value in cell E9 is the text
string "Fee1". The total of Fee1 for Client 1 should be 20 in this
example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0),
E9, OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1, 0))

According to the Excel help, #REF! errors mean that the OFFSET goes
beyond the edges of the worksheet, but I don't think I am doing this.
Any help would be gratefully appreciated, either in fixing this formula
or offering a better solution. Whatever formula I use will need to be
copied down for several hundred clients. Thank you in advance!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Thank you very much, Biff. Your formula works. I do greatly appreciate
your time, and the time all of the contributors to this forum. You all do
great work!

I also figured out the error in my original formula. I was using zero as
the width parameter in the two offset functions, but obviously both the
width and height parameters must be at least 1. It was not so obvious to
me last night.



"T. Valko" wrote in message
...
I suppose one option could be to put in an extra hidden
column that had the client name in every row.
I would prefer not to have to remember to do this.

Well, you might want to reconsider that after you see this formula!

Let's assume you want to sum Fee1 for a specific client.

Client names in column A starting in cell A9.
Fee category in column E starting in cell E9
Fee amounts in column F starting in cell F9.

There *must* be an empty cell at the end of the range in column E.

H9 = the client to sum for like Client1
I9 = fee category like Fee1

=SUMPRODUCT(--(OFFSET(E9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0))=I9),OFFSET(F9,MATCH(H9,A9:A30,0)-1,,MATCH(TRUE,OFFSET(E9,MATCH(H9,A9:A30,0)-1,,30)="",0)))

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Thanks for the fast reply, Biff!

In the formula I posted, the row reference in the formula will start
with the first row for the first client, Row 9 in this example. The
number of rows in the formula (row 9 - row 60) is intended to be well
beyond the number of rows any client would ever have so I should not
need to ever update the formulas for this reason. The last row
associated with a given client is identified by the blank row (Row 18 in
this example). It was my intention to start with row 9 and then look
down to the first blank cell in Column E to find the end of the data for
the client.

I thought that when I copied this formula down to the other clients and
pasted it into the appropriate row for each client, the range of rows
associated with each client would be calculated automatically by the
offset function due to the relative referencing.

On my worksheet, the client names are in Column A, on the first row of
the data for that client. In my example, the name "Client 1" would be
only in cell A9.

I suppose one option could be to put in an extra hidden column that had
the client name in every row. Then I could use SUMPRODUCT to do a
double test for the client name and the fee name, and this SUMPRODUCT
formula would be easy to write. One disadvantage of this method would
be the need to add the client name to each new row as fees are added. I
would prefer not to have to remember to do this.

If this does not answer your question, please let me know what other
information I need to provide.

Thank you again very much!



"T. Valko" wrote in message
...
How do you identify fees that are related to a specific client?

--
Biff
Microsoft Excel MVP


"Lewis Clark" wrote in message
...
Hello

I am trying to find a way to use SUMIF with OFFSET on a range of
variable length. My data is organized in this manner:

Each row represents one fee charged to a client on a particular date.

Column E contains the names of the fees that are charged. Each client
has at least one entry for Fee1 through Fee5. The number of rows for
any particular client may increase during the year.

A blank row will separate clients from each other (Row 18 in this
example). There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1).
Rows 19 - 23 represent the fees charged to the next client (Client 2).

Column F in each row may have an entry or it may be blank.

Col E Col F

Row 9 Fee1 10
Row 10 Fee2 20
Row 11 Fee3 30
Row 12 Fee4 40
Row 13 Fee5 50
Row 14 Fee1 10
Row 15 Fee4
Row 16 Fee1
Row 17 Fee2 20
Row 18 blank
Row 19 Fee1 10
Row 20 Fee2 20
Row 21 Fee3 30
Row 22 Fee4 40
Row 23 Fee5 50

I am trying to create a formula to add up all of the Fee1 charges for
Client 1. Since the number of rows for Client 1 will vary, I am
trying to use the MATCH function to identify the number of rows
associated with Client 1 by looking for the first blank cell in Column
E at the bottom of the data for each client.

The following formula gives a #REF! error. When I step through the
formula, the two OFFSET functions appear to show the correct inputs of
OFFSET(E9, 0, 0, 9, 0) and OFFSET(F9, 0, 0, 9, 0), but each one
individually gives the #REF! error. The value in cell E9 is the text
string "Fee1". The total of Fee1 for Client 1 should be 20 in this
example.

=SUMIF(OFFSET(E9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1,
0), E9, OFFSET(F9, 0, 0, MATCH(TRUE,INDEX(ISBLANK(E9:E60),0,0),0)-1,
0))

According to the Excel help, #REF! errors mean that the OFFSET goes
beyond the edges of the worksheet, but I don't think I am doing this.
Any help would be gratefully appreciated, either in fixing this
formula or offering a better solution. Whatever formula I use will
need to be copied down for several hundred clients. Thank you in
advance!