| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem using SUMPRODUCT | New Users to Excel | |||
| sumproduct problem | Excel Worksheet Functions | |||
| sumproduct problem | New Users to Excel | |||
| sumproduct problem | Excel Worksheet Functions | |||
| Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Linear Mode
