Help on Formula

[Karthik]

I've data copied from a .CSV file and pasted it to an excel sheet.
Column A-G is copied from .CSV File and Column H is calculated using formula
(=D1/F1/24) to find the items per hour.
Column "D" is number of Items and Column "F" is Time in HH:MM:SS format.

But when I use the formula
"=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and
Same formula works well if I manually enter data in Column "H" instead of
using (=D1/F1/24)..

Could someone please help me with this.

Thanks..

 

[Max]

You've got #DIV/0! errors in col H. Clean it up, or include an error trap for
the formula in col H to return errors as zeros, eg:
=IF(ISERROR(D1/F1/24),0,D1/F1/24)

Alternatively, as-is, you could try this array-entered* equivalent:
=SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*(B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999)))
*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Karthik]

Col "H" does'nt have Zeros or #DIV/0!,
Sumproduct formula gives #DIV/0! error if i calculate Col "H" with formula
(=D1/F1/24).
Example:
D F H
10 0:10:00 60.0000
If col "D1" is 10 and Col "F1" is 10 Minutes and col "H" is
(D1/F1/24)=60.0000 per hour

Sumproduct formula works well if I manually enter 60.0000 in Column H and it
gives #DIV/0! if its calculated by the formula (=d1/f1/24)

 

[David Biddulph]

You can't get #DIV/0! from
=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)
unless column H contains #DIV/0! errors. You'd better look again.

 

[Karthik]

Thanks David, I failed to notice that..

 

[Max]

Karthik,
Doesn't the alternative array-entered expression given in my earlier
response:
=SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*(B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999)))
do the job for you, as-is ? I tested it fine here, it evaluates correctly in
the face of possible #DIV/0! errors in col H. And it should work for you just
as well.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---