Sum of last few available lines with data

[monaldo12]

Hi,

Basically very simple for you. I need a help with a formula that can sum up the last three fields:

10 0 10 0 10

Sum of last three should be 20, so it has to take zero's into account. However, my searching field won't be filled in till the last line:

10 0 10 10 10 . .

So Sum should be just 30, the dots will be empty. So where their is an empty space I need it to look further back, where there are zero's it needs to take them into account.

 

[godsaaint]

Use this array formula(Confirm with Ctrl-Shift-Enter):

=SUM(Z1:INDEX(A1:Z1,LARGE(COLUMN(A1:Z1)*(A1:Z1<>""),3)))

Note: In this example I'm assuming there are no values further than column Z, if there are, adjust accordingly.

 

[monaldo12]

Use this array formula(Confirm with Ctrl-Shift-Enter):

=SUM(Z1:INDEX(A1:Z1,LARGE(COLUMN(A1:Z1)*(A1:Z1<>""),3)))

Note: In this example I'm assuming there are no values further than column Z, if there are, adjust accordingly.

Hmm doesn't seem to work, outcome is zero. Even if I fill in some numbers for example in Y1 and Z1.....

 

[godsaaint]

The formula definitely works, must be something else. Could you copy-paste row 1 here?

 

[monaldo12]

The formula definitely works, must be something else. Could you copy-paste row 1 here?

1	2	3	4	5	6	7
10	0	10	10	10

<tbody>
</tbody>

 

[cornflakegirl]

I think if you change
(A1:Z1<>"")
to
(A1:Z1>0)
it will work