SUMPRODUCT returning no answer

[AJPendragon]

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?

 

[Max]

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))

 

[Bernie Deitrick]

Try this to coerce the text values to numbers:

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

Bernie