Sumproduct with blanks

[EZ]

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.

 

[Eduardo]

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]

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.

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)

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.

 

[EZ]

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.

Look at this array:

--([MyModel.xls]ByPlant!$AF$10:$AF$1231)

I'll bet you're missing something!

--
Biff
Microsoft Excel MVP

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.

 

[Luke M]

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!*

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.

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)

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.

 

[T. Valko]

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

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.

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)

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.

 

[JoeU2004]

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 -----

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.

 

[T. Valko]

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

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!*

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.

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)


:

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.

 

[Jacob Skaria]

Between 61 and 1231 you are having a unexpected value in one of the reference
cells...

If this post helps click Yes

 

[EZ]

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.

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!*

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.

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)


:

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.