Using SUMIF and OFFSET togeather

Hi Guys,

I have used the following formula to SUM column "I", I have used the OFFSET function because we have to insert new rows, I read that using the OFFSET if I inserted, say, 3 rows the I20 would change to I23, and it does, it works fine.

SUM(I6:OFFSET(I20,-1,0))

Now, I have a column "C" that store names and I used the SUMIF function to get a total,

=SUMIF(C6:C20,"Silver Dawn",I6:I20) this also works fine until I insert new rows.

So I have tried a combination of both the SUMIF & OFFSET functions, like

=IF(C6:OFFSET(C20,-1,"SilverDawn"),I6:OFFSET(I20,-1,0))

Can this be achieved, can the 2 functions be used together, if so can you help with the syntax?

TIA
Keith

ps
Sorry if this post appears twice, I "thought" I posted it a couple of hours ago but there was no trace of it so I wrote it again

Ok Guys,

First of all thank you for your responses and sorry for the confusion.

I have updated my profile to say I am using Office 2010.

The row numbers were hypothetical in my post, so I'll try to explain.

I have a button on the sheet that, when clicked, adds an additional 6 rows with all formatting/calculations and inserts these rows above the row that contains my SUMs.

1. your formula =OFFSET(I20,-1,0) actually ranges up I19. but you wanted I6:I20 right?

In this example the SUM(I6:I20) is the TOTAL which is in I20. If I press the button the OFFSET(I20,-1,0) should then read OFFSET(I26,-1,0) hit the button again and it should read OFFSET(I32,-1,0) as I said in my post, this is working fine in columns F through to X. It is in column AA I am having the problem, where I am trying to use the SUMIF and the OFFSET together.

Hi Izandol,

Thank you for your input the calc you posted (once I put a space between Silver & Dawn) worked perfectly.

=SUMIF(C6:OFFSET(C20,-1,0),"Silver Dawn",I6:OFFSET(I20,-1,0))

Strangely so does

SUMIF(C6:C20,"Silver Dawn",I6:I20) but I HAD to include the row I20 which is where the actual SUM is.

I would like to thank all who helped.