|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
June 11th, 2009, 05:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with blanks
I have been using SUMPRODUCT for a while, and so far it's great. I'm trying
to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change. 
|
|
#2
June 11th, 2009, 05:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
Look at this array:
--([MyModel.xls]ByPlant!$AF$10:$AF$1231) I'll bet you're missing something! -- Biff Microsoft Excel MVP "EZ" wrote in message ... I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#3
June 11th, 2009, 06:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
Hi,
I assume you want to pull the information from AF10:AF1231 try =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),([MyModel.xls]ByPlant!$AF$10:$AF$1231) "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#4
June 11th, 2009, 06:22 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
So, you are basically omitting the ' "" ' portion on the last part of the
formula... It didn't work. As I said the formula works as is if I break the range into smaller ranges. The only thing I can think of is (and someone can help me with this): The data is organized in lists... for every 52 rows there's a header (text) and a footer (number) subtotal then a few blank rows. Will the text header cause the formula not to work? -- when u change the way u look @ things, the things u look at change. "Eduardo" wrote: Hi, I assume you want to pull the information from AF10:AF1231 try =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),([MyModel.xls]ByPlant!$AF$10:$AF$1231) "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#5
June 11th, 2009, 06:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
See my previous post. I have text headers for every group on merged cell...
-- when u change the way u look @ things, the things u look at change. "T. Valko" wrote: Look at this array: --([MyModel.xls]ByPlant!$AF$10:$AF$1231) I'll bet you're missing something! -- Biff Microsoft Excel MVP "EZ" wrote in message ... I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#6
June 11th, 2009, 07:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
Don't put the double dash in front of your array with numbers. The double
dash says treat it as 1's & 0's, which is not what you want to happen. ,--([MyModel.xls]ByPlant!$AF$10:$AF$1231), needs to be just ,([MyModel.xls]ByPlant!$AF$10:$AF$1231), Also, if you think about the logic, there's no need for all the "" checks. If a cell equals C5, it can't be equal to "", unless C5 = "". -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "EZ" wrote: So, you are basically omitting the ' "" ' portion on the last part of the formula... It didn't work. As I said the formula works as is if I break the range into smaller ranges. The only thing I can think of is (and someone can help me with this): The data is organized in lists... for every 52 rows there's a header (text) and a footer (number) subtotal then a few blank rows. Will the text header cause the formula not to work? -- when u change the way u look @ things, the things u look at change. "Eduardo" wrote: Hi, I assume you want to pull the information from AF10:AF1231 try =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),([MyModel.xls]ByPlant!$AF$10:$AF$1231) "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#7
June 11th, 2009, 07:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
for every 52 rows there's a header (text) and a
footer (number) subtotal then a few blank rows. Will the text header cause the formula not to work? Look at this array: --([MyModel.xls]ByPlant!$AF$10:$AF$1231) I'll bet you're missing something! That's what I was trying to "point" you towards. If you have a TEXT entry in the range AF10:AF1231: for every 52 rows there's a header (text) The above array is where you're getting the #VALUE! error. Assume AF10 = TEXT header... --(AF10) = #VALUE! -- Biff Microsoft Excel MVP "EZ" wrote in message ... So, you are basically omitting the ' "" ' portion on the last part of the formula... It didn't work. As I said the formula works as is if I break the range into smaller ranges. The only thing I can think of is (and someone can help me with this): The data is organized in lists... for every 52 rows there's a header (text) and a footer (number) subtotal then a few blank rows. Will the text header cause the formula not to work? -- when u change the way u look @ things, the things u look at change. "Eduardo" wrote: Hi, I assume you want to pull the information from AF10:AF1231 try =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),([MyModel.xls]ByPlant!$AF$10:$AF$1231) "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#8
June 11th, 2009, 07:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
"EZ" wrote:
I keep getting #Value error. I presume you are getting the #VALUE error from the 2nd-to-last argument, --([MyModel.xls]ByPlant!$AF$10:$AF$1231). Change that to simply [MyModel.xls]ByPlant!$AF$10:$AF$1231 -- not "--" and no parentheses. Normally, text is not permitted in arithmetic expressions, e.g. --AF10:AF1231. But SUMPRODUCT tolerates arguments that are text, treating them as zero. With the change above, you can also remove the last argument, --([MyModel.xls]ByPlant!$AF$10:$AF$1231""). I presume you added that in an vane attempt to avoid the #VALUE error. It becomes superfluous with the above change. Finally, you seem to misunderstand why "--" is used sometimes. It is only needed to convert boolean values (TRUE and FALSE) to numeric values, which SUMPRODUCT requires. Thus, you need "--" in SUMPRODUCT arguments only before comparison expressions and logic functions like AND and OR. ----- original message ----- "EZ" wrote in message ... I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#9
June 11th, 2009, 07:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
Don't put the double dash in front of your array
with numbers.The double dash says treat it as 1's & 0's, --([MyModel.xls]ByPlant!$AF$10:$AF$1231) Well, not quite. The array gets coerced to 1 and 0 if there's a logical comparrison taking place. Like --(AF10="x"). At first I thought the OP was getting a count so I thought they were missing a logical test: --([MyModel.xls]ByPlant!$AF$10:$AF$1231=???) Everyone else seems to think they're doing a sum. If the array AF$10:$AF$1231 contains TEXT then --(array) = #VALUE! -- Biff Microsoft Excel MVP "Luke M" wrote in message ... Don't put the double dash in front of your array with numbers. The double dash says treat it as 1's & 0's, which is not what you want to happen. ,--([MyModel.xls]ByPlant!$AF$10:$AF$1231), needs to be just ,([MyModel.xls]ByPlant!$AF$10:$AF$1231), Also, if you think about the logic, there's no need for all the "" checks. If a cell equals C5, it can't be equal to "", unless C5 = "". -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "EZ" wrote: So, you are basically omitting the ' "" ' portion on the last part of the formula... It didn't work. As I said the formula works as is if I break the range into smaller ranges. The only thing I can think of is (and someone can help me with this): The data is organized in lists... for every 52 rows there's a header (text) and a footer (number) subtotal then a few blank rows. Will the text header cause the formula not to work? -- when u change the way u look @ things, the things u look at change. "Eduardo" wrote: Hi, I assume you want to pull the information from AF10:AF1231 try =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),([MyModel.xls]ByPlant!$AF$10:$AF$1231) "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
#10
June 11th, 2009, 08:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with blanks
Between 61 and 1231 you are having a unexpected value in one of the reference
cells... If this post helps click Yes --------------- Jacob Skaria "EZ" wrote: I have been using SUMPRODUCT for a while, and so far it's great. I'm trying to use it on data range with blanks. My data is arranged as 52 weeks/rows for each location, but there are a few blank rows in between locations (not completely blank, but there's row for subtotal and other blanks). I'm using sumproduct to pull data from this workbook into another workbook. Here's my formula: =SUMPRODUCT(--([MyModel.xls]ByPlant!$B$10:$B$1231=C5),--([MyModel.xls]ByPlant!$B$10:$B$1231""),--([MyModel.xls]ByPlant!$C$10:$C$1231=D5),--([MyModel.xls]ByPlant!$C$10:$C$1231""),--([MyModel.xls]ByPlant!$M$10:$M$1231=$E$3),--([MyModel.xls]ByPlant!$M$10:$M$1231""),--([MyModel.xls]ByPlant!$AF$10:$AF$1231),--([MyModel.xls]ByPlant!$AF$10:$AF$1231"")) I keep getting #Value error. But if change the range for the first location from 10:1231 to 10:61 I will get the correct result. I will be pulling data from many workbooks, and I'd really like to do the entire range and ignore the blanks. Can someone tell me why my formula is not working, or is there another way to incorporate the blanks into sumproduct? For other reasons, the data cannot be rearranged... in other words those blank rows have to be there. Thanks. -- when u change the way u look @ things, the things u look at change.
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
