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