| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
