SUMIF with OFFSET

[Lewis Clark]

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!

 

[T. Valko]

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

 

[Lewis Clark]

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]

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)))

 

[Lewis Clark]

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]

You're welcome. Thanks for the feedback!