ExcelBanter - SUM and IF statements

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUM and IF statements (https://www.excelbanter.com/excel-worksheet-functions/116592-sum-if-statements.html)

SUM and IF statements

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob

SUM and IF statements

You've got your test criteria mixed up:

Instead of this:
=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Try this:
=SUM(IF($G$2:$G$83-9,IF($G$2:$G$83<9,I2:I83)))
Note: that is an array formula, so commit it with [ctrl]+[shift]+[enter]

OR
You might conisider using SUMPRODUCT, instead. :
(Just [enter].....not [ctrl]+[shift]+[enter])
=SUMPRODUCT(($G$2:$G$839)*($G$2:$G$83<-9)*I2:I83)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob

SUM and IF statements

Sorry, but this doesn't work. The SUM(IF statement produces the sum of values
between -9 and 9. I want the sum of values above 9 and below -9.

The SUMPRODUCT formula doesn't produce the correct answer, either.

Bob

"Ron Coderre" wrote:

You've got your test criteria mixed up:

Instead of this:
=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Try this:
=SUM(IF($G$2:$G$83-9,IF($G$2:$G$83<9,I2:I83)))
Note: that is an array formula, so commit it with [ctrl]+[shift]+[enter]

OR
You might conisider using SUMPRODUCT, instead. :
(Just [enter].....not [ctrl]+[shift]+[enter])
=SUMPRODUCT(($G$2:$G$839)*($G$2:$G$83<-9)*I2:I83)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob

SUM and IF statements

Sorry, but this doesn't work. The SUM(IF statement produces the sum of
values between -9 and 9. I want the sum of values above 9 and below -9.<<

Oh...you mean like you asked for in the first place? <g

Then, try this:

=SUMPRODUCT((($G$2:$G$839)+($G$2:$G$83<-9))*I2:I83)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Sorry, but this doesn't work. The SUM(IF statement produces the sum of values
between -9 and 9. I want the sum of values above 9 and below -9.

The SUMPRODUCT formula doesn't produce the correct answer, either.

Bob

"Ron Coderre" wrote:

You've got your test criteria mixed up:

Instead of this:
=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Try this:
=SUM(IF($G$2:$G$83-9,IF($G$2:$G$83<9,I2:I83)))
Note: that is an array formula, so commit it with [ctrl]+[shift]+[enter]

OR
You might conisider using SUMPRODUCT, instead. :
(Just [enter].....not [ctrl]+[shift]+[enter])
=SUMPRODUCT(($G$2:$G$839)*($G$2:$G$83<-9)*I2:I83)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob

SUM and IF statements

Great. thanks.

"Ron Coderre" wrote:

Sorry, but this doesn't work. The SUM(IF statement produces the sum of
values between -9 and 9. I want the sum of values above 9 and below -9.<<

Oh...you mean like you asked for in the first place? <g

Then, try this:

=SUMPRODUCT((($G$2:$G$839)+($G$2:$G$83<-9))*I2:I83)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Sorry, but this doesn't work. The SUM(IF statement produces the sum of values
between -9 and 9. I want the sum of values above 9 and below -9.

The SUMPRODUCT formula doesn't produce the correct answer, either.

Bob

"Ron Coderre" wrote:

You've got your test criteria mixed up:

Instead of this:
=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Try this:
=SUM(IF($G$2:$G$83-9,IF($G$2:$G$83<9,I2:I83)))
Note: that is an array formula, so commit it with [ctrl]+[shift]+[enter]

OR
You might conisider using SUMPRODUCT, instead. :
(Just [enter].....not [ctrl]+[shift]+[enter])
=SUMPRODUCT(($G$2:$G$839)*($G$2:$G$83<-9)*I2:I83)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob

SUM and IF statements

Thanks for the feedback, Bob....I appreciate it.

BTW....Here's a shorter version:
=SUMPRODUCT((ABS(G2:G83)9)*I2:I83)

***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Great. thanks.

"Ron Coderre" wrote:

Sorry, but this doesn't work. The SUM(IF statement produces the sum of
values between -9 and 9. I want the sum of values above 9 and below -9.<<

Oh...you mean like you asked for in the first place? <g

Then, try this:

=SUMPRODUCT((($G$2:$G$839)+($G$2:$G$83<-9))*I2:I83)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Sorry, but this doesn't work. The SUM(IF statement produces the sum of values
between -9 and 9. I want the sum of values above 9 and below -9.

The SUMPRODUCT formula doesn't produce the correct answer, either.

Bob

"Ron Coderre" wrote:

You've got your test criteria mixed up:

Instead of this:
=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Try this:
=SUM(IF($G$2:$G$83-9,IF($G$2:$G$83<9,I2:I83)))
Note: that is an array formula, so commit it with [ctrl]+[shift]+[enter]

OR
You might conisider using SUMPRODUCT, instead. :
(Just [enter].....not [ctrl]+[shift]+[enter])
=SUMPRODUCT(($G$2:$G$839)*($G$2:$G$83<-9)*I2:I83)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"bob" wrote:

Column G, rows 2-83 have numbers ranging from -30 to +30.
Column I has a list of numbers I want to add when the values in Column G are
greater than 9 or less than -9.

I have tried this but do not get the correct result:

=SUM(IF($G$2:$G$839,IF($G$2:$G$83<-9,I2:I83)))

Can anyone help?
Thanks,
Bob