J,
In Z2, enter the formula
=IF(OFFSET($F$3,(ROW()-ROW())*5,0)="","",SUMIF(OFFSET($F$3:$L$3,(ROW()-ROW($Z$2))*5,0),"<0"))
and copy down.
HTH,
Bernie
MS Excel MVP
"JBoyer" <(E-Mail Removed)> wrote in message
news:C2295AB2-86CE-443C-8AB1-(E-Mail Removed)...
>I should have been more clear with my example, here is the formula I'm using
> =IF(ISBLANK(F3:L3),"",SUM(IF(F3:L3<0,F3:L3,FALSE))). This formula is in cell
> Z2.
>
> Now in cell Z3 I want =IF(ISBLANK(F8:L8),"",SUM(IF(F8:L8<0,F8:L8,FALSE))).
>
> "Bernie Deitrick" wrote:
>
>> In Cell B1, enter a formula like
>>
>> =INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2
>>
>> Note that the B1 and $B$1 are strictly needed - they should be the address
>> of the first cell that this is entered in, so that you don't have to limit
>> where you put the formula, and so that you can insert rows above and still
>> have the formula work.
>>
>> This is the part that steps through a range by 5 rows everytime the formula
>> is copied down by one row
>>
>> INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)
>> That becomes
>> INDEX($A$1:$A$1000,(1-1)*5+1,1)
>> INDEX($A$1:$A$1000,1,1)
>> or just A1
>>
>> Copied down, you get
>>
>> INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1)
>> That becomes
>> INDEX($A$1:$A$1000,(2-1)*5+1,1)
>> INDEX($A$1:$A$1000,1*5+1,1)
>> INDEX($A$1:$A$1000,6,1)
>> or A6
>>
>> etc.
>>
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "JBoyer" <(E-Mail Removed)> wrote in message
>> news:A556AFDF-D566-4294-A736-(E-Mail Removed)...
>> > Now my actually case is more complicated than this, but I think if someone
>> > explains this to me, I'll be fine.
>> >
>> > say i have a formula in cell b1 that is =a1*2
>> > now in b2 i would have =a6*2
>> > and in b3 i would have =a11*2
>> >
>> > and continue to increase the cell value by five.
>> >
>> > Can i do this be just dragging down the corner rather than entering them
>> > manually?
>>
>>
>>
Similar Threads
