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 |
#11
|
|||
|
|||
Sumproduct with blanks
Thanks Luke. It worked. Just removed the dashes in front of the last portion
of the formula, and it worked. Good to know this, because when I changed my range to the first list only 10:61 it did work with the dashes added. Thanks. -- when u change the way u look @ things, the things u look at change. "Luke M" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|