=(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

If you get a #DIV/0! error it's because this portion evaluates to 0:

SUMPRODUCT(A1:A5,$C1:$C5)

Try this:

=IF(SUMPRODUCT(A1:A5,$C1:$C5),SUMPRODUCT(B1:B5,A1: A5)/(SUMPRODUCT(A1:A5,$C1:$C5),"")

--
Biff
Microsoft Excel MVP


"Harsh Bahal" wrote in message
...
Suppose col A1 to A5 is rate, Col B1 to B5 is cases and col C1 to C5 case
weight.In cell C6 i have entered a formula
=(SUMPRODUCT(B1:B5,A1:A5)/(SUMPRODUCT(A1:A5,$C1:$C5)))

to get the per kilogram ( or pound) rate. Which does give the correct
answer, but if there are no figures entered in col A,B or C then the
formula
returns #div/0!, which is i want avoid. I am working on 100 rows like
this.

"Mike H" wrote:

Post your sumproduct formula and a description of what your doing

Mike

"Harsh Bahal" wrote:

Hi !

I want enter a sumproduct formula involving three ranges. However if
any of
the range does not have a value the formula returns a #div/0! value. I
want
to avoid that. Pl help.