Thread: Increase range by one using a dynamic range

If you notice on the attachment in columns I:J I am summing up at each change in the word Major in column I.

Instead of the hard coding the range I would like to make the range dynamic, but not sure how to increase the range by one.

Here is at least one part of the formula

SUM($J3:$J$14)

which to make dynamic I have gone with

SUM($J3:INDEX(J:J,MATCH(BigNum,J:J))

but this only returns

SUM($J3:$J$13)

but I need it to be down to the last row +1 which equals $J$14

So wouldn't that be?

=SUM($J3:INDEX(J:J,MATCH(BigNum,J:J)+1))

Thanks NBVC,

That's what I thought it would be, but the result retuns a zero.

When I step through the formula it shows the correct result, but the outcome only displays zero.

Also, when I drag the fomula down I get a circular reference at E8

=IF($D2="Major",IF(COUNTIF($D3:INDEX(D:D,MATCH(REPT("z",255),D:D)+1),"Major"),
SUM($E3:INDEX($E3:INDEX(E:E,MATCH(BigNum,E:E)+1),MATCH("Major",$D3:INDEX(D:D,MATCH(REPT("z",255),D:D )+1),0)-1)),SUM($E3:INDEX(E:E,MATCH(BigNum,E:E)+1))),C2)

Does this work?

=IF(D2="Major",SUM(C3:INDEX($C3:$C$14,MATCH(TRUE,INDEX($C3:$C$14="",0),0))),C2)

Yes NBVC this seems to work fine. I tried it the way you provided and then expanded in to be dynamic with:

=IF(D2="Major",SUM(C3:INDEX($C3:INDEX(C:C,MATCH(BigNum,C:C)+1),MATCH(TRUE,INDEX($C3:INDEX(C:C,MATCH( BigNum,C:C)+1)="",0),0))),C2)

I will try to implement this on my project now...much appreciated

Hi again NBVC,

This will work if I had an additional column, but I am stuck with the format of the spreadsheet so therefore all should be summed in one column.

I should have made the example different, ex., column C is not one value as displayed it is a multiplication of two cells.

So in the example it could be considered B2 * C2.

Instead of using column C I was hoping to use column E where I can sum up all of the Sub's between the Major's so E2 will be the sum of all the Sub's inbetween Major's or if D2 equals Major then B2*C2.

My only other alternative was to use

=IF($D2="Major",
IF(COUNTIF($D3:$D$14,"Major"),
SUM($E3:INDEX($E3:$E$14,MATCH("Major",$D3:$D$14,0)-1)),
SUM($E3:$E$14)),B2*C2)

and make the E14 and D14 something like 2000 which should cover the entire range.

Hope this helps

How about?

=IF(D2="Major",SUM(E3:INDEX($E3:$E$14,MATCH(1,INDEX(($D3:$D$14="")+($D3:$D$14="Major"),0),0)-1)),B2*C2)

NBVC,

Thank you for your patience, it seems to be working now. All the best...