SUMPRODUCT to skip 0's

[osteelero]

i am trying to run a sumproduct matching date, team, and adding offhours and dividing by schedhrs - i keep getting div/0 erros - tried it all - am i far off?

tried these 2 variations

=SUMPRODUCT((($A$2=Data!$D$2:$D$311)*(M1=Data!$B$2:$B$311)*((Data!$I$2:$I$311)+(Data!$J$2:$J$311)+(Data!$K$2:$K$311)+(Da ta!$L$2:$L$311)+(Data!$M$2:$M$311)+(Data!$N$2:$N$311)/(Data!$F$2:$F$311<>0))))


=SUMPRODUCT((($A$2=Data!$D$2:$D$311)*(M1=Data!$B$2:$B$311)*--(Data!$N$2:$N$311>0,((Data!$I$2:$I$311)+(Data!$J$2:$J$311)+(Data!$K$2:$K$311)+(Data!$L$2:$L$311)+(Data!$M$2:$M$311)+(Data!$N$2:$N$311)))))

 

[tgrunwald]

It's going to be tough to figure out why you are getting that error without knowing what is in those cells. Can you give an example of what kind of data you are trying to use sumproduct with?

Just a generic example will work.

Also I can see that you definately are not using SUMPRODUCT correctly.

 

[osteelero]

this seems to be closer (but still getting the occasional div/0)

=SUMPRODUCT((($A$2=Data!$D$2:$D$303)*(M1=Data!$B$2:$B$303)*((Data!$I$2:$I$303)+(Data!$J$2:$J$303)+(Data!$K$2:$K$303)+(Data!$L$2:$L$303)+(Data!$M$2:$M$303)+(Data!$N$2:$N$303))))/(SUMPRODUCT((($A$2=Data!$D$2:$D$303)*(M1=Data!$B$2:$B$303)*((Data!$F$2:$F$303)))))


sample data:

D is division name (Sales, service, etc)
B is date
I:L are the offstates hours for hours absent, vaca, etc (2, 4, 8, etc) -- can be 0
F is hours scheduled (8.25, 8, etc) -- can be 0

 

[osteelero]

actually tweeked it and it looks like it is working now


think using both sumproducts to do the math with worked rather than trying to sumproduct with the divide in there

sorry for the trouble! been staring at this data too long