Try SUMPRODUCT(--(A2:A65536="Toys"),--(B2:B65536=b2),c2:c65536)
Let us know if it helps
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andy" wrote in message
...
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