Sum x number of right cells after finding first cell more than 0.

[Learn]

Hi

I am finding a solution (formula) to automatically sum (3 or 12) cells
on the right after encountering first right cell greater than 0.

Currently, this is done manually. (eg. 3.9 is the sum of 2.9 (Aug) +
0.5 (Sep) + 0.4 (Oct).)

Question is there a way to do this automatically because there are
thousands of rows..and this will drive me crasy in maintaining this
worksheet effectively. Thanks.

First First
3M 12M Jul Aug Sep Oct Nov Dec ....................
-------------------------------------------------------------------------------------------------------------------------------------------
3.9 5.4 0.0 2.9 0.5 0.4 0.3 0.2 ....................
1.0 2.2 0.0 0.9 0.0 0.2 0.0 0.4 ....................
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ....................
3.2 5.2 0.0 0.0 0.0 1.9 1.3 0.9 ....................
3.1 5.1 0.0 0.0 0.0 0.5 1.7 0.9 ....................
2.6 3.9 0.0 0.0 0.0 0.4 1.1 1.1 ....................
........................
........................

Ivan

 

[Roger Govier]

Hi

One way would be with the array entered formula
{=SUM(OFFSET(INDEX(2:2,MATCH(TRUE,2:2>0,2:2)),0,0,1,3))}

To enter, or amend, an array formula use Control+Shift+Enter (CSE) not just
Enter
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.

The 3 at the end of the formula is for 3 months.
Change this value for any other number of months required

 

[Domenic]

For the first 3 months, try the following formula that needs to be
confirmed with CONTROL+SHIFT+ENTER...

=SUM(INDEX(C2:H2,MATCH(TRUE,C2:H2>0,0)):INDEX(C2:H2,MATCH(TRUE,C2:H2>0,0)
+3-1))

For the first 12 months, replace +3 with +12. Adjust the ranges
accordingly.

Hope this helps!

 

[Learn]

For the first 3 months, try the following formula that needs to be
confirmed with CONTROL+SHIFT+ENTER...

=SUM(INDEX(C2:H2,MATCH(TRUE,C2:H2>0,0)):INDEX(C2:H2,MATCH(TRUE,C2:H2>0,0)
+3-1))

For the first 12 months, replace +3 with +12. Adjust the ranges
accordingly.

Hope this helps!











- Show quoted text -

The solution to this issue is given by Roger and Domenic as follows:
{=SUM(OFFSET(INDEX(2:2,MATCH(TRUE,2:2>0,2:2)),0,0,1,3))} = using CTRL
+ SHIFT + ENTER

Thanks and it works perfectly. Cheers!

 

[Herbert Seidenberg]

If the formula was placed in the position shown
in the example, wouldn't you get a circular reference?
If the string of zeros extended into the last 12 cells
of your data and there was unrelated data following
your data, would you not get a wrong answer?