SUM Formula giving #VALUE due to cells populated with " - "

[EllaM]

=SUM(AQ13+AQ14-AQ15)
This is giving me an error of #Value as the contents of some of the cells is a dash ( - )
How can i tell excel to consider the dash as a 0 without changing the format of the cell or updating the cell value to 0?

i tried to embed an IF formula but this did not work. example below:
=SUM(IF(AQ13=" - ",0,AQ13)AQ13+AQ14-AQ15)

[Fluff13]

Hi & welcome to the board.
How about

Please Login or Register  to view this content.

[EllaM]

Thank you!

That wouldnt work as i am trying to add the first 2 cells and then subtract the third

[joeu2004]

First, there is no good reason to write SUM(AQ13+AQ14-AQ15). Simply write AQ13+AQ14-AQ15 or SUM(AQ13,AQ14)-AQ15.

If you suspect that some cells might have text (that Excel cannot interpret as numbers), you can use the N() function to treat them as zero. For example:

=SUM(AQ13,AQ14) - N(AQ15)

Here, I take advantage of the fact that SUM also ignores (or treats as zero) cell references (AQ13, AQ14) that might not be numeric.

But if only AQ13 might be non-numeric, the following would suffiice:

=SUM(AQ13,AQ14) - AQ15

[Fluff13]

i am trying to add the first 2 cells and then subtract the third

Oops missed that, but joeu2004 has shown a few options.