Apache OpenOffice Community Forum

Hello,
I am trying to create a formula type code for my spreadsheet which will go loop through certain cells to if if they contain a value and if they do then it will add a number to another cell, is this possible? I was thinking of an IF NOT type code but im not sure if I am correct:

z = 2
IF A2 NOT 'null' then
A35 = A35 + B(z)
z + 1

then this should loop until it gets to A70 for example

is this possible and if so how do I go about implementing this into my spreadsheet, I have not done any coding to spreadsheets before.

Thanks
Richard

The SUMIF function should do want you want... nearly:

Let's say that range A2:A24 contains either 0 or 1, and the range B2:B24 some values. If you want to sum all the values from the range B2:B24 where the corresponding A cell on the same row contains 1, then use the formula:
=SUMIF(A2:A24; 1; B2:B24)

I am not sure that SUMIF can test 'is not NULL'' value other than using "<>0" (with double quotes) as a second parameter in the formula above.

im not sure that will work.. let me explain some more
for example:
COL1 W z
34 4
23 3
23 3
21 2

23 2

I need to loop through col1 and if there is a value in the cell then to take the value of the corresponding cell in W and then to add that to cell Z

does that make sense?

thanks
Richard

1000 is a reasonable high number to cover all the input values.
Z1: =SUMIF($A$1:$A$1000;"<>0";$W$1:$W$1000) ["<>0" includes text]

alternatively:
Z1: =SUMPRODUCT($A$1:$A$1000;$W$1:$W$1000) [adds W for all numbers <>0 in A
Z1: =SUMPRODUCT(NOT(ISBLANK($A$1:$A$1000));$W$1:$W$1000) [adds W for all non-blanks in A, including text values]
Z1: =SUMPRODUCT(ISNUMBER($A$1:$A$1000);$W$1:$W$1000) [adds W for any number in A]


Cumulate in column Z:
Z1 =IF($A1;$W1;0) [start value]
Z2 =IF($A2;$W2;0)+$Z1 [add previous if condition matches]
[copy down Z2]
$A1, $A2, ... evaluate to TRUE for all numbers <>0. Texts and blanks yield FALSE.

For the concept of "this row" and "previous value" refer to: Re: [Tutorial] Absolute, rela…

Z1: =SUMPRODUCT(NOT(ISBLANK($A$1:$A$1000));$W$1:$W$1000)


Thanks this works fantastically


Richard