SUMPRODUCT - Ignore blank rows
 

I'm pretty familiar with sumproduct and vlookup. This time I have blank rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is there
a way to ignore these rows, or another formula that will lookup the value? I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

I can't tell from your description, what you want to do
could you give an example of what you want to transfer
what problem do the blank rows give you?
can you give an example of the rows 10-61 equation which does work
I assume you have as part of your sumproduct --(A10:A1751<"")
"sahafi" wrote:

I'm pretty familiar with sumproduct and vlookup. This time I have blank rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is there
a way to ignore these rows, or another formula that will lookup the value? I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

If I'm understanding this, you want to sum ByPlant!$AH$10:$AH$1751 when the
other criteria are met?
If so, try this:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1),--(ByPlant!$B$10:$B$1751=Utilization!$AS$1),--(ByPlant!$M$10:$M$1751=Utilization!$W3),ByPlant!$A H$10:$AH$1751)

HTH,
Paul



"sahafi" wrote in message
...
Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second
criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM
contains
my
location number (the same number copied down on 52 rows), on Col AH is
my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52
rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4),
(2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of
my
projects. In the 53rd row of each project, I have the total, then I
have 3
blank rows between each project and the other (not completely blank,
they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if
I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the
value?
I
don't want to enter a formula with different range for each project,
and I
don't want to copy the data to another sheet in a list format (it's
huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

try:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1),--(ByPlant!$A$10:$A$1751<""),--(ByPlant!$B$10:$B$1751=Utilization!$AS$1),--(ByPlant!$B$10:$B$1751<""),--(ByPlant!$M$10:$M$1751=Utilization!$W3),--(ByPlant!$M$10:$M$1751<""),--(ByPlant!$AH$10:$AH$1751),--(ByPlant!$AH$10:$AH$1751<""))



"sahafi" wrote:

Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

Thanks for the reply, but that didn't work either.


Thanks.
--
If u change the way u look @ things, the things u look at change.


"Toppers" wrote:

try:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1),--(ByPlant!$A$10:$A$1751<""),--(ByPlant!$B$10:$B$1751=Utilization!$AS$1),--(ByPlant!$B$10:$B$1751<""),--(ByPlant!$M$10:$M$1751=Utilization!$W3),--(ByPlant!$M$10:$M$1751<""),--(ByPlant!$AH$10:$AH$1751),--(ByPlant!$AH$10:$AH$1751<""))



"sahafi" wrote:

Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

Can you send sample w/book with expected results?
toppers at REMOVETHISjohntopley.fsnet.co.uk.

"sahafi" wrote:

Thanks for the reply, but that didn't work either.


Thanks.
--
If u change the way u look @ things, the things u look at change.


"Toppers" wrote:

try:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1),--(ByPlant!$A$10:$A$1751<""),--(ByPlant!$B$10:$B$1751=Utilization!$AS$1),--(ByPlant!$B$10:$B$1751<""),--(ByPlant!$M$10:$M$1751=Utilization!$W3),--(ByPlant!$M$10:$M$1751<""),--(ByPlant!$AH$10:$AH$1751),--(ByPlant!$AH$10:$AH$1751<""))



"sahafi" wrote:

Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.

SUMPRODUCT - Ignore blank rows
 

Toppers: It's working now. I have copied portion of the data into another
workbook, and it did work. I came to find out that I have two blocks of data
that showing zero values, and the value I needed to lookup is a calculated %
value. So as soon as I overwrote the '#DIV/0!' with 1% the rest of the
formulas work.
Thanks.

--
If u change the way u look @ things, the things u look at change.


"Toppers" wrote:

Can you send sample w/book with expected results?
toppers at REMOVETHISjohntopley.fsnet.co.uk.

"sahafi" wrote:

Thanks for the reply, but that didn't work either.


Thanks.
--
If u change the way u look @ things, the things u look at change.


"Toppers" wrote:

try:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1),--(ByPlant!$A$10:$A$1751<""),--(ByPlant!$B$10:$B$1751=Utilization!$AS$1),--(ByPlant!$B$10:$B$1751<""),--(ByPlant!$M$10:$M$1751=Utilization!$W3),--(ByPlant!$M$10:$M$1751<""),--(ByPlant!$AH$10:$AH$1751),--(ByPlant!$AH$10:$AH$1751<""))



"sahafi" wrote:

Here's my formula:

=SUMPRODUCT(--(ByPlant!$A$10:$A$1751=Utilization!$AR$1,ByPlant!$ A$10:$A$1751<"")*(ByPlant!$B$10:$B$1751=Utilizati on!$AS$1,ByPlant!$B$10:$B$1751<"")*(ByPlant!$M$10 :$M$1751=Utilization!$W3,ByPlant!$M$10:$M$1751<"" )*(ByPlant!$AH$10:$AH$1751,ByPlant!$AH$10:$AH$1751 <""))


--
when u change the way u look @ things, the things u look at change.


"PCLIVE" wrote:

Without seeing your current SUMPRODUCT formula, couldn't you just add an
array that would be < ""?

=SUMPRODUCT(--(A10:A1751="first criteria"),--(B10:B1751="second criteria"))

Actually, come to think of it, if the row is blank or doesn't match the
critria specified, then it shouldn't affect the formula or result at all.
Give a bit of an example and the formula you currently use.

Regards,
Paul


"sahafi" wrote in message
...
I'm pretty familiar with sumproduct and vlookup. This time I have blank
rows
in between my data list, and I couldn't get either formula to work.
In sheet1:
Col A has my period data (1-13) and ColB my wks data (1-4), ColM contains
my
location number (the same number copied down on 52 rows), on Col AH is my
claculated field that I need to lookup and insert on 'Sheet2'.
I have different projects on sheet1, and each project consists of 52 rows
of
data. So in columns (A,B) I will have (1,1), (1,2), (1,3), (1,4), (2,1),
(2,2), (2,3),..(13,4). So columns A & B have the same data for all of my
projects. In the 53rd row of each project, I have the total, then I have 3
blank rows between each project and the other (not completely blank, they
do
hold data, that not relevant to this task, so I can't delete them).
The sumproduct work fine If I use the range 10:61, but will not work if I
use the range 10:1751 and that because of the blank rows in between. Is
there
a way to ignore these rows, or another formula that will lookup the value?
I
don't want to enter a formula with different range for each project, and I
don't want to copy the data to another sheet in a list format (it's huge).
Any help is greatly appreciated.

Thanks.
--
If u change the way u look @ things, the things u look at change.