Sumproduct #Value! Problem
 

I have a sumproduct formula that is returning #Value! when the range includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

Use a custom format, for instance

0.00;-0.00;"-"

will display zero as -

or if these are money use accounting format


--


Regards,


Peo Sjoblom


"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

Not sure I completely understand you, but maybe something like this:

=SUMPRODUCT(--(Data!A70:A78=Sheet1!B72)+(Data!A70:A78="-"),(Data!H70:H78))

HTH,
Paul

--

"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

Thanks...I just noticed these are formated as text....I tried your custom
format below...didn't work. Is there any way I can convert these to numbers
and make them zeros? Thanks again

"Peo Sjoblom" wrote:

Use a custom format, for instance

0.00;-0.00;"-"

will display zero as -

or if these are money use accounting format


--


Regards,


Peo Sjoblom


"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

If they are text format and not general with text in them, do an
editreplace and replace - with 0
after you have change the format to the custom one


--


Regards,


Peo Sjoblom



"deeds" wrote in message
...
Thanks...I just noticed these are formated as text....I tried your custom
format below...didn't work. Is there any way I can convert these to
numbers
and make them zeros? Thanks again

"Peo Sjoblom" wrote:

Use a custom format, for instance

0.00;-0.00;"-"

will display zero as -

or if these are money use accounting format


--


Regards,


Peo Sjoblom


"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that
it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

=SUMPRODUCT(--(Data!A70:A78=Sheet1!B72),Data!H70:H78)

With this syntax, text will be ignored/treated as 0.



deeds wrote:

I have a sumproduct formula that is returning #Value! when the range includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

--

Dave Peterson

Sumproduct #Value! Problem
 

Excellent! This works perfect. Thanks much!

"PCLIVE" wrote:

Not sure I completely understand you, but maybe something like this:

=SUMPRODUCT(--(Data!A70:A78=Sheet1!B72)+(Data!A70:A78="-"),(Data!H70:H78))

HTH,
Paul

--

"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

That would work.....thanks for your help!

"Peo Sjoblom" wrote:

If they are text format and not general with text in them, do an
editreplace and replace - with 0
after you have change the format to the custom one


--


Regards,


Peo Sjoblom



"deeds" wrote in message
...
Thanks...I just noticed these are formated as text....I tried your custom
format below...didn't work. Is there any way I can convert these to
numbers
and make them zeros? Thanks again

"Peo Sjoblom" wrote:

Use a custom format, for instance

0.00;-0.00;"-"

will display zero as -

or if these are money use accounting format


--


Regards,


Peo Sjoblom


"deeds" wrote in message
...
I have a sumproduct formula that is returning #Value! when the range
includes
cells with a "-" instead of a zero. How can I get around this so that
it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

Sumproduct #Value! Problem
 

Even better....thanks for the solution!

"Dave Peterson" wrote:

=SUMPRODUCT(--(Data!A70:A78=Sheet1!B72),Data!H70:H78)

With this syntax, text will be ignored/treated as 0.



deeds wrote:

I have a sumproduct formula that is returning #Value! when the range includes
cells with a "-" instead of a zero. How can I get around this so that it
treats the "-" like zeros? My formula:

=SUMPRODUCT((Data!A70:A78=Sheet1!B72)*(Data!H70:H7 8))

Thanks in advance

--

Dave Peterson