Excel 2007 : Sum 4 consecutive cells

[bakemono]

Hi all,

I am trying to sum 4 consecutive cells that are above 0.

Here is an example:


0,1,2,3,0,4

The sum I want is the first 4 non-0 numbers, which from that example, would be 6.

Thank you all in advanced for your help.

[NBVC]

So if your cells are in say A1:A6, wouldn't that be =SUM(A1:A4)

[bakemono]

Actually, let me clarify

what I actually want is the sum of the first 4 non-0 numbers.

another example would be:


0,0,1,0,0,2,0,4,0,4


In this example, I want the first non-0, in this case 1, and the next 3 numbers to be summed. The result would be 1+0+0+2 which equates to 3.

The number does not matter after the first non-0 is added, so long as the first number is greater than 0.

Hope this is clearer.

I should also note that the spreadsheet that I am working with contains multiple columns so a simple =sum would not work. Thanks again.

[NBVC]

So if the values are in A1:A10 try either:

=SUM(OFFSET(INDEX($A$1:$A$10,MATCH(TRUE,INDEX(A1:A10<>0,0),0)),0,0,4,1))

or

=SUM(INDEX($A$1:$A$10,MATCH(TRUE,INDEX(A1:A10<>0,0),0)):INDEX($A$1:$A$10,MATCH(TRUE,INDEX(A1:A10<>0,0),0)+3))

The Offset version is shorter, but is volatile.

[bakemono]

Thank you NBVC! Your formula worked perfectly.