#div/0! Error
 

Someone please let me know what's wrong with this formula.
=SUMPRODUCT(--(A$2:A$99=L$5),--(J$2:J$99=M$6),--(C$2:C$99=K30),--(H$2:H$99$L$2),H$2:H$99)

This formula works well when data is entered maully in cells of Column H,
but when I copy from Column I (=$D$1/$F$1/24) and paste the value in column H
the above formula shows #div/0!

Column D is items and column F is Time taken to Produce the item and Column
H is Items/hr.

Thanks for your help in advance..

--
Karthi

#div/0! Error
 

Hi,

The formula is fine, I suspect you have div/0 errors in the range, try this
alternative for populating column H

=IF($F$1<0,$D$1/$F$1/24,"")

Mike

"Karthik" wrote:

Someone please let me know what's wrong with this formula.
=SUMPRODUCT(--(A$2:A$99=L$5),--(J$2:J$99=M$6),--(C$2:C$99=K30),--(H$2:H$99$L$2),H$2:H$99)

This formula works well when data is entered maully in cells of Column H,
but when I copy from Column I (=$D$1/$F$1/24) and paste the value in column H
the above formula shows #div/0!

Column D is items and column F is Time taken to Produce the item and Column
H is Items/hr.

Thanks for your help in advance..

--
Karthi

#div/0! Error
 

Dear Karthik

The bottom line is when ever you get a DIV/0 error; check the references in
your formula and handle the references which could be a probable zero using
IF condition..

If this post helps click Yes
---------------
Jacob Skaria


"Karthik" wrote:

Someone please let me know what's wrong with this formula.
=SUMPRODUCT(--(A$2:A$99=L$5),--(J$2:J$99=M$6),--(C$2:C$99=K30),--(H$2:H$99$L$2),H$2:H$99)

This formula works well when data is entered maully in cells of Column H,
but when I copy from Column I (=$D$1/$F$1/24) and paste the value in column H
the above formula shows #div/0!

Column D is items and column F is Time taken to Produce the item and Column
H is Items/hr.

Thanks for your help in advance..

--
Karthi

#div/0! Error
 

Thanks Mike,
Now it says #VALUE!
Column H is Time converted in to Numbers, is that affecting the formula?

--
Karthi


"Mike H" wrote:

Hi,

The formula is fine, I suspect you have div/0 errors in the range, try this
alternative for populating column H

=IF($F$1<0,$D$1/$F$1/24,"")

Mike

"Karthik" wrote:

Someone please let me know what's wrong with this formula.
=SUMPRODUCT(--(A$2:A$99=L$5),--(J$2:J$99=M$6),--(C$2:C$99=K30),--(H$2:H$99$L$2),H$2:H$99)

This formula works well when data is entered maully in cells of Column H,
but when I copy from Column I (=$D$1/$F$1/24) and paste the value in column H
the above formula shows #div/0!

Column D is items and column F is Time taken to Produce the item and Column
H is Items/hr.

Thanks for your help in advance..

--
Karthi