Hi Bernard,

Yes, b2 is a cell reference.
The formula always worked until a week ago, I did not change anything and
just copied the same formula to the new row.
Anyway, the double negative way works now and I'll use this syntax from now
on to avoid any sudden surprise.

Thanks again
Andy

"Bernard Liengme" wrote in message
...
In your formula (and mine!) is b2 a cell reference?
Not sure why yours did not work - I tried it and it seemed OK
the double negative way is generally preferred - it follows the SUMPRODUCT
syntax more closely
=SUMPRODUCT(array-A, array-B,....)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andy" wrote in message
...
Thank you all for the replies. Just woke up and saw the replies, thanks.

Bernard, your solution works, please can you tell me what went wrong with
my formula ?

Best regards
Andy

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