I want Sumproduct to not show 0 but -

[Niffe783]

Hello,

I hope some one can help me with this tricky one.

When i Use sumproduct formula i want it to show "-" or or nothing at all insted of 0. Is that possible. I have attached a excel sheet to show what i mean.

Thanks.

[DonkeyOte]

I would strongly advise using a Custom Number Format to mask 0 with either hyphen or blank rather than double evaluating the SUMPRODUCT.

I am assuming you're using SUMPRODUCT rather than SUMIF because you're data is stored in another file which might be closed

[Niffe783]

Thats correct I am using other workbooks that are closed. I did not understand the conditional formating. Could you pleas show?

[DonkeyOte]

We're not talking about Conditional Formatting (altogether different [super volatile]) but rather Customising a Number Format.

Instead of formatting your result cell as General, Number etc you assign a Customised Format - see here for a more graphic example

[Niffe783]

Ok I got it to work but the thing is that I am using a grapfh and the point of not showing 0 is to have the starting point of the graph not in 0 but the given number by the "sumproduct formula"

[DonkeyOte]

OK - might have been worth mentioning that a little earlier

What you could do is use something like:

Please Login or Register  to view this content.

What will then happen is 0's will become #N/A and won't plot - obviously you need to revise the SUMPRODUCT element per your own formula.

[Niffe783]

What is 9.99E+307? Dont cant get it to work!

[DonkeyOte]

Sorry, I didn't think to alter that for your Locale - you would use 9,99E+307

On an aside please do not quote prior posts in their entirety - this simply clutters your thread and the board in general.

edit:

attached is just a proof of concept re: handling 0 for charting purposes - hopefully from this you can work out how to implement in your own model
(functions & delimiters will adjust per your locale on open)

[scottylad2]

this type of construct should work

=IF(OR(ISERROR('SUMPRODUCT(($F$28:$F$32=!$B$1)*1;($C$28:$C$32))),'SUMPRODUCT(($F$28:$F$32=!$B$1)*1;($C$28:$C$32))=0),"-",'SUMPRODUCT(($F$28:$F$32=!$B$1)*1;($C$28:$C$32)))

[DonkeyOte]

@scottylad2, the point here is that avoiding double evaluation (triple in the above) is something of a no-brainer where SUMPRODUCTs are involved

FWIW, there's simply no need to evaluate the SUMPRODUCT three times.

Should you want to persist with repeat calculations I would advise you divide 1 by the SUMPRODUCT result and test for numeric result.

The above is based on the fact that an error or SUMPRODUCT result of 0 would generate an Error, in UK syntax terms:

Please Login or Register  to view this content.

But again to reiterate I would suggest avoiding the double evaluation altogether and use an alternative construct:

Please Login or Register  to view this content.

which would also generate either Number or #N/A error whilst evaluating the SUMPRODUCT once only

Note use of NA is for sake of chart plot - hyphen/null string etc would behave no differently to 0 in that regard.