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.