#value!

[lukerees83]

What does it mean when #VALUE! is displayed in a cell? If this is displayed in one of my columns it seems to mess up my formula and also return #VALUE! into the cell I put the formula into. Is there a way to make any cell with #VALUE! in it just clear its contents of place a zero there instead and rectify this?

 

[doofusboy]

Just add an if statement to your formula; something like this:

=IFERROR(your_formula_here,"")

This will either present the result of your formula or a blank if an error occurs.

 

[lukerees83]

The formula I am inserting in here is a sumproduct formula. It is therefore returning a zero value every time because when it goes down the list of thing it is supposed to be counting some of those cells have erros in them, so it simply returns "" for the whole thing.

Is there a way to add on to a sumproduct formula something that says "when counting please ignore if the cell is empty or contains an error or contains #ref"?

Hope that makes sense

 

[ruby123]

I had same problem. I just do not understand why Excel and Microsoft is doing this to users; it seems that they want average users to be dependant on their Visual Basic advanced programming. I used Lotus and Excel nearly all my life and with every new upgrade they take away simple stuff and logical stuff expecting data to be perfectly entered in a spreadsheet; what about downloaded data and situation such as you are facing!
Sorry, here what I did:
=IF(CELL("contents",F11)="",your sum_formula here including zero for the empty cell,your sum_formula here) [F11 is cell you have data in or blank]
Hope this helps.

 

[Aladin Akyurek]

The formula I am inserting in here is a sumproduct formula. It is therefore returning a zero value every time because when it goes down the list of thing it is supposed to be counting some of those cells have erros in them, so it simply returns "" for the whole thing.

Is there a way to add on to a sumproduct formula something that says "when counting please ignore if the cell is empty or contains an error or contains #ref"?

Hope that makes sense

Care to post the formula you have?