SUMPRODUCT returning no answer
 

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?

SUMPRODUCT returning no answer
 

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?

SUMPRODUCT returning no answer
 

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?