Eliminating "0" Chart Points on Cells that are "Blank"

[jmenzel30]

Hello,

I'm having an issue that I believe the most difficult part is asking the question. So I'm going to try to do pictures. However as a quick rundown, I have 4 tables. General structure of tables is:

	January	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
2022	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -

So there are 4 separate rows setup like that. Each Cell is linked to an external workbook. Each data set in the table is linked to a different external source (This is because each company has their own report). Then there is a 5th sheet that tracks a total of the 4 sheets. The current formula is as follows: =IFERROR(IF(SUM(B$9+B$20+B$29+B$36)<>"",(B$9+B$20+B$29+B$36),""),"") which looks great in the table. But if you look at the chart below it is being plotted on zero. How can I make that dynamic and not plot zero? I think it's probably in the formula because so far I have tried going to select data and hide zero's/blanks, I've done that setting in the options>advanced>display>don't show zero's. Not having any luck. Anybody have better ideas?

Thank you in advance for any of your help!

 

[BSALV]

you have strings(=text) in your cells, try SUM(B$9,B$20,B$29,B$36).
The formula 'll skip the strings.
Do you have values in that 5t sheet ?

 

[jmenzel30]

you have strings(=text) in your cells, try SUM(B$9,B$20,B$29,B$36).
The formula 'll skip the strings.
Do you have values in that 5t sheet ?

values will be in the fifth sheet at month end when the values for January are filled in for the other 4 tables. So there is a formula in the cell. But no displayed value until the numbers are placed on the sheet. Does that make sense?

 

[BSALV]

=IF(SUM(B$9,B$20,B$29,B$36)<>0,sum(B$9,b$20,B$29,B$36),NA())

 

[jmenzel30]

=IF(SUM(B$9,B$20,B$29,B$36)<>0,sum(B$9,b$20,B$29,B$36),NA())

So when I use that formula, I get #VALUE in the cell, and it still plots a 0 on the Chart. I've tried formatting all cells to "number" and that doesn't seem to do it either. So that's why I built in the iferror, but its still plotting on my chart.

 

[jmenzel30]

So when I use that formula, I get #VALUE in the cell, and it still plots a 0 on the Chart. I've tried formatting all cells to "number" and that doesn't seem to do it either. So that's why I built in the iferror, but its still plotting on my chart.

I have also tried Ctrl+Shift+Enter to get rid of the #Value and that doesn't seem to work either.

 

[BSALV]

as long as the month isn't finished, the sum is 0, i assume and a 0 is replaced by NA() (= an error).
The IfERROR may not replace that value, so delete that part of the formula.
What values have the cells B$9,B$20,B$29 and B$36 now ?

 

[jmenzel30]

as long as the month isn't finished, the sum is 0, i assume and a 0 is replaced by NA() (= an error).
The IfERROR may not replace that value, so delete that part of the formula.
What values have the cells B$9,B$20,B$29 and B$36 now ?

I did indeed take that part out. Currently in B$9,B$20,B$29 and B$36 they display as a blank cell, but they have a link to a cell on another worksheet. So I think that's creating the problem, but Idk how to fix it. My only thought is if I try something with the "isblank" Function.