I have a SUMPRODUCT formula that I know is OK but returns the answer 00:00:00

=SUMPRODUCT(--(Sheet2!B4:B573=A6),(Sheet2!C4:C573))

Sheet 2 is full of cells with times (HH:MM:SS) which have been pasted in
from another excel worksheet.

However, if I go into each cell in sheet 2 and return, the formula then
works €“ its as though the cell value is hidden.

Hope you can help?

Think the values in Sheet2 could be text numbers

Try it like this, with an implicit coercion:
=SUMPRODUCT((Sheet2!B4:B573=A6)*(Sheet2!C4:C573))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AJPendragon" wrote:
I have a SUMPRODUCT formula that I know is OK but returns the answer 00:00:00

=SUMPRODUCT(--(Sheet2!B4:B573=A6),(Sheet2!C4:C573))

Sheet 2 is full of cells with times (HH:MM:SS) which have been pasted in
from another excel worksheet.

However, if I go into each cell in sheet 2 and return, the formula then
works €“ its as though the cell value is hidden.

Hope you can help?

Try this to coerce the text values to numbers:

=SUMPRODUCT((((Sheet2!B4:B573)*1)=A6)*((Sheet2!C4: C573)*1))

Bernie


"AJPendragon" wrote in message
...
I have a SUMPRODUCT formula that I know is OK but returns the answer
00:00:00

=SUMPRODUCT(--(Sheet2!B4:B573=A6),(Sheet2!C4:C573))

Sheet 2 is full of cells with times (HH:MM:SS) which have been pasted in
from another excel worksheet.

However, if I go into each cell in sheet 2 and return, the formula then
works - it's as though the cell value is hidden.

Hope you can help?