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.