Sumproduct suddenly not working

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

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

The arguments for SUMPRODUCT() are separated by commas, rather than
stars. The first one resulted in an error because the proper arguments
are not present. My guess is the second works because there were
*enough* arguments to return an answer: Excel evaluated the A=Toys *
B=Date as the first argument and column C as the second argument.

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" <atkchung@z6.com> wrote in message
news:ui1Kun9gFHA.2880@TK2MSFTNGP09.phx.gbl...
> 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:c65536)
>
> 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:C65536) 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
>

Dave,

Andy's formula worked OK when I tried it. I couldn't come up with any ideas
why he might be getting the result he's getting.

As for commas and asterisks, usually, when we use SUMPRODUCT in this way,
we're not using the "product" part of the function. When we separate the
boolean expressions with asterisks, we're multiplying them, so there's only
one argument to the SUMPRODUCT, hence no commas.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Dave O" <CycleZen@yahoo.com> wrote in message
news:1120844710.637415.77480@f14g2000cwb.googlegroups.com...
> The arguments for SUMPRODUCT() are separated by commas, rather than
> stars. The first one resulted in an error because the proper arguments
> are not present. My guess is the second works because there were
> *enough* arguments to return an answer: Excel evaluated the A=Toys *
> B=Date as the first argument and column C as the second argument.
>

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" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:e1NH1o%23gFHA.3164@TK2MSFTNGP15.phx.gbl...
> 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" <atkchung@z6.com> wrote in message
> news:ui1Kun9gFHA.2880@TK2MSFTNGP09.phx.gbl...
>> 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:c65536)
>>
>> 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:C65536) 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
>>
>
>

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" <atkchung@z6.com> wrote in message
news:u8aJ$FDhFHA.2372@TK2MSFTNGP14.phx.gbl...
> 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" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:e1NH1o%23gFHA.3164@TK2MSFTNGP15.phx.gbl...
>> 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" <atkchung@z6.com> wrote in message
>> news:ui1Kun9gFHA.2880@TK2MSFTNGP09.phx.gbl...
>>> 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:c65536)
>>>
>>> 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:C65536) 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
>>>
>>
>>
>
>

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" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:uRfXj2ahFHA.3936@TK2MSFTNGP10.phx.gbl...
> 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" <atkchung@z6.com> wrote in message
> news:u8aJ$FDhFHA.2372@TK2MSFTNGP14.phx.gbl...
>> 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" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> news:e1NH1o%23gFHA.3164@TK2MSFTNGP15.phx.gbl...
>>> 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" <atkchung@z6.com> wrote in message
>>> news:ui1Kun9gFHA.2880@TK2MSFTNGP09.phx.gbl...
>>>> 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:c65536)
>>>>
>>>> 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:C65536) 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
>>>>
>>>
>>>
>>
>>
>
>

Since SUMPRODUCT((A2:A65536="Toys")*(B2:B65536=B1),C2:C65536) 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:c65536)
>
> 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:C65536) 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
>
>
>