Sumproduct #Value! Problem

  • Thread starter Thread starter deeds
  • Start date Start date
D

deeds

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

Thanks in advance
 
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
 
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
 
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
 
If they are text format and not general with text in them, do an
edit>replace and replace - with 0
after you have change the format to the custom one


--


Regards,


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

With this syntax, text will be ignored/treated as 0.
 
That would work.....thanks for your help!

Peo Sjoblom said:
If they are text format and not general with text in them, do an
edit>replace and replace - with 0
after you have change the format to the custom one


--


Regards,


Peo Sjoblom
Click to expand...
 
Excellent! This works perfect. Thanks much!

PCLIVE said:
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
Click to expand...
 
Even better....thanks for the solution!

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

With this syntax, text will be ignored/treated as 0.
Click to expand...
 
Back
Top