Wrong Data Type error

[highflight1985]

I have two date values which did the following to calculate the time difference between them:

=IF(AND(A4<>0,B4<>0,C4<>0,D4<>0),SUM(K4/(((C4+D4)-(A4+B4))*24)),"")

where C4 is a date, D4 is a time; same for A4 and B4 respectively.

How do I take the result of that and use it in another formula on another sheet by referencing the cell? When I do, I get "VALUE" error, saying it's of the wrong data type...

 

[highflight1985]

The formula causing the error is:

=SUM(('Load Entry'!$C$4:$C$563<=$A$28)*('Load Entry'!$C$4:$C$563>=$A$27)*'Load Entry'!$Y$4:$Y$563)

Where Y is the referenced cell I was talking about...

 

[T. Valko]

I have two date values which did the following to calculate the time difference between them:

=IF(AND(A4<>0,B4<>0,C4<>0,D4<>0),SUM(K4/(((C4+D4)-(A4+B4))*24)),"")

where C4 is a date, D4 is a time; same for A4 and B4 respectively.

How do I take the result of that and use it in another formula on another sheet by referencing the cell? When I do, I get "VALUE" error, saying it's of the wrong data type...

It depends on how you're using the result of that formula.

You're probably getting the error when the above formula returns the formula blank "" which is a TEXT string.

You may need to first test the result of the above formula to see if it's a number. For example, if that formula is entered in cell A1...

=IF(COUNT(A1),your_other_formula_here,"")

 

[T. Valko]

The formula causing the error is:

=SUM(('Load Entry'!$C$4:$C$563<=$A$28)*('Load Entry'!$C$4:$C$563>=$A$27)*'Load Entry'!$Y$4:$Y$563)

Where Y is the referenced cell I was talking about...

Don't use the SUM syntax. Use the SUMPRODUCT version we showed you in the other thread.

=SUMPRODUCT(--('Load Entry'!$C$4:$C$563>=$A$27),--('Load Entry'!$C$4:$C$563<=$A$28),'Load Entry'!$Y$4:$Y$563)

When using that syntax, it'll ignore TEXT entries in the sum range 'Load Entry'!$Y$4:$Y$563.

 

[highflight1985]

Thanks!

You really are an MVP!

 

[T. Valko]

Thanks!

You really are an MVP!

You're welcome. Thanks for the feedback!

 

[highflight1985]

You're welcome. Now maybe you can answer my other question at:

http://www.mrexcel.com/forum/showthread.php?t=556136

I know, a shameless ad for another question of mine. I'm such a bad person. LOL