Determine range to sum based on a given number

[loudnoiseman]

Hi,

I have a formula in V1 that counts how many positive values are in column B before turning negative.

In W1, I want to sum all of those values beginning with B2, ignoring everything beyond the first negative.

So if for example V1 returns 12, the formula should essentially know to construct =SUM(B2:B13). If B2 is negative then zero.

I'll be continuously sorting different columns so that count and range to sum will always be changing.

I imagine something like: "=SUM(B2:B"&(PositiveCountFormula)+1

Thanks!

 

[Trebor76]

Hi loudnoiseman,

Try this:

=SUM(INDIRECT("B2:B"&V1+1))

Regards,

Robert

 

[loudnoiseman]

Excellent, thanks Robert!

 

[Trebor76]

You're welcome