SUMPRODUCT - Ignore blank rows

[Guest]

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.

 

[Guest]

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

 

[PCLIVE]

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

 

[Guest]

Here's my formula:

=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<>""))

 

[PCLIVE]

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!$AH$10:$AH$1751)

HTH,
Paul

 

[Guest]

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

 

[Guest]

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


Thanks.

 

[Guest]

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

 

[Guest]

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.