Since SUMPRODUCT((A2:A65536="Toys")*(B2:B65536=B1),C2:C6 5536) works, I
presume that there are non-empty cells in C2:C65536 that are neither
numbers nor coercible text.

What results do you get from COUNT(C2:C65536) and COUNTA(C2:C65536)?

Jerry

Andy wrote:

Hi,

I have a database of few hundred rows recording the money spent on items of
fun, below shows the first 3 rows of it to illustrate my question.

A B C D
1 Toys May 6, 2005 8.00 34.00
2 Books May 6, 2005 23.00
3 Toys May 6, 2005 26.00

D1 result is from formula
SUMPRODUCT((A2:A65536="Toys")*(B2:B65536=b2)*c2:c6 5536)

The Sumproduct formula has been working fine for months, but the formula
isn't working any more and returns #value!. I couldn't figure out why.
I checked the database and am sure all data is entered correctly, items are
entered as text, date is entered as date and money is entered as number.
I tried and changed the formula to
SUMPRODUCT((A2:A65536="Toys")*(B2:B65536=B1),C2:C6 5536) and it works. I
would appreciate if someone can tell me what happened.

The Sumproduct function is such a powerful function and I have learned a lot
about it from this NG.
Thanks in advance
Andy