Excel - SUMPRODUCT - Ignore blank rows

Asked By donotspa on 08-Jun-07 01:19 PM
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.




PCLIVE replied on 08-Jun-07 01:35 PM
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
b replied on 08-Jun-07 01:34 PM
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<>"")
donotspa replied on 08-Jun-07 01:55 PM
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<>""))


--
when u change the way u look @ things, the things u look at change.
PCLIVE replied on 08-Jun-07 02:25 PM
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
Topper replied on 08-Jun-07 02:30 PM
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<>""))
donotspa replied on 12-Jun-07 05:43 PM
Thanks for the reply, but that did not work either.


Thanks.
--
If u change the way u look @ things, the things u look at change.
Topper replied on 13-Jun-07 01:57 AM
Can you send sample w/book with expected results?
toppers at REMOVETHISjohntopley.fsnet.co.uk.
donotspa replied on 13-Jun-07 01:11 PM
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.