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.