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

L

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
 
R

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
 
D

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!
 
L

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!
 
H

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top