## [Solved] IF NOT code for spreadsheet?

Creating a macro - Writing a Script - Using the API

### [Solved] IF NOT code for spreadsheet?

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
Last edited by richjsmith on Tue Apr 22, 2008 1:32 pm, edited 1 time in total.
richjsmith

Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm

### Re: IF NOT code for spreadsheet?

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.
LibreOffice 4.2.3.3. on Ubuntu 14.04

squenson
Volunteer

Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

### Re: IF NOT code for spreadsheet?

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
richjsmith

Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm

### Re: IF NOT code for spreadsheet?

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…
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 25764
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: IF NOT code for spreadsheet?

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

Thanks this works fantastically

Richard
richjsmith

Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm