| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
URGENT: Please Advise. SumProduct and Operand Question
Good morning Everybody,
My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1:B8) Doesnt work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates €œ#Value€ =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
=SUMPRODUCT((A1:A20=2000)*(A1:A20<=2090)*(B1:B20) )
"Brent E" wrote: Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1:B8) Doesnt work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates €œ#Value€ =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
Thanks for the prompt response Jonathan.
I pasted your formula into a cell, but is giving me a "#Value" error. What else do I need to do? Please advise. Thanks "Jonathan Cooper" wrote: =SUMPRODUCT((A1:A20=2000)*(A1:A20<=2090)*(B1:B20) ) "Brent E" wrote: Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1:B8) Doesnt work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates €œ#Value€ =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
FYI, you can test a number of particular values like so
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{2000,2010,2020,2030,2040}, 0))),B2:B20) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Brent E" wrote in message ... Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B 8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1: B8) Doesn't work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates "#Value" =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
Great. That worked well. Thanks to you both for your good help; have a great
week. Thanks again, Brent "Bob Phillips" wrote: FYI, you can test a number of particular values like so =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{2000,2010,2020,2030,2040}, 0))),B2:B20) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Brent E" wrote in message ... Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B 8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1: B8) Doesn't work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates "#Value" =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
|
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
Ctrl-Shift-Enter is used instead of just Enter to signal to Excel that the
formula is an array formula. This is required when using an array of values, or cells, in a function that normally expects a single value, or cell, such as IF. If you do it correctly, Excel will surround the formula in braces, {...}. This is done by Excel, you do not need to. When you edit it, they disappear, until you commit in the same way. -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Brent E" wrote in message ... Ardus, Please explain more about what Ctrl+Shift+Enter does. And do I do this before or after using a SumIf formula? Thanks. "Ardus Petus" wrote: =SUM(IF(A1:A8=2000,IF(A1:A8<=2090,B1:B8))) enter as array formula (Ctrl+Shift+Enter) HTH -- AP "Brent E" a écrit dans le message de news: ... Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B 8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1: B8) Doesn't work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates "#Value" =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
URGENT: Please Advise. SumProduct and Operand Question
Excellent. Thanks for the explanation and the help, guys. I really appreciate
it. Brent "Bob Phillips" wrote: Ctrl-Shift-Enter is used instead of just Enter to signal to Excel that the formula is an array formula. This is required when using an array of values, or cells, in a function that normally expects a single value, or cell, such as IF. If you do it correctly, Excel will surround the formula in braces, {...}. This is done by Excel, you do not need to. When you edit it, they disappear, until you commit in the same way. -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Brent E" wrote in message ... Ardus, Please explain more about what Ctrl+Shift+Enter does. And do I do this before or after using a SumIf formula? Thanks. "Ardus Petus" wrote: =SUM(IF(A1:A8=2000,IF(A1:A8<=2090,B1:B8))) enter as array formula (Ctrl+Shift+Enter) HTH -- AP "Brent E" a écrit dans le message de news: ... Good morning Everybody, My CFO asked me to see if I could find a way to save us a lot of time and work by finding an excel formula that will do the following. I would greatly appreciate any assistance. Suppose you have sample values like this: A B 2000 (20,000) 2040 - 9000 (29,000) 2010 100,000 2030 - 9010 20,000 9020 - 9040 400000 We would like to use a formula that can do this: Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090), For all values within this range, sum their Column B Components in a single cell somewhere on the spreadsheet, say N2. So in this example, two values are null so (20,000) and 100,000 would be summed in N2. I tried using SumProduct. I can get SumProduct to work if I use only one test range: =SUMPRODUCT(--(A1:A8=2000),(B1:B8)) But this only tests for one of the values in the range between 2000 and 2090 and Returns a value of -20,000 (which is correct but excludes the rest of the range). I also tried using an operator, but am not certain of the proper syntax. I experimented w/ these: =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B 8) Returns a 0 value =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1: B8) Doesn't work, generates error message =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1 :A8=2040),B1:B8) Generates "#Value" =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8) Returns a 0 value Please advise. Cordially, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
