Lookup Help

[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]

see the responses you had in excel.worksheetfunctions yesterday.
Did they not work?
If so, respond within that thread so others can se what has already been
offered to you as a solution.

 

[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

==================================================

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

It works perfectly. Thank you very much. Cheers!

Ivan
==================================================

 

[Roger Govier]

Again, I ask, did you not find the solution I posted worked for you?
It worked fine for me.

In case you didn't see it, I repeat it below slightly modified
One way would be with the array entered formula
{=SUM(OFFSET(INDEX(C2:Z2,MATCH(TRUE,C2:Z2>0,C2:Z2)),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
Change the range C2:Z2 to suit
Enter the formula in A2 and copy the formula down the page as far as you
require