I have a formula in a cell(A) that reads the information from another cell(B)
using the Vlookup function. However, some of these B cells contain a formula
with no results in them, resulting in A displaying the #VALUE! error. is
there a way to ignore those cells that contain no values?

I am also trying to add up all of the A cells but obviously get the #VALUE!
as a result because of the previous errors. Anyway to count these VALUE
cells as zero or circumvent in any way?

thanks

Try this:

=IF(ISERROR(your_formula),"",your_formula)

If you are using SUM to add up the cells, then "" will be fine - if,
instead, you are referring to specific cells like =A1 + A2 + A3, then
you will need to change the "" in the middle to 0 (zero).

Hope this helps.

Pete

hotelmasters wrote:
I have a formula in a cell(A) that reads the information from another cell(B)
using the Vlookup function. However, some of these B cells contain a formula
with no results in them, resulting in A displaying the #VALUE! error. is
there a way to ignore those cells that contain no values?

I am also trying to add up all of the A cells but obviously get the #VALUE!
as a result because of the previous errors. Anyway to count these VALUE
cells as zero or circumvent in any way?

thanks

thanks for the help - what about the second part - can i ignore the value
error when adding them together with a formula of =sum('Q1:Q4'!D9)?


"Pete_UK" wrote:

Try this:

=IF(ISERROR(your_formula),"",your_formula)

If you are using SUM to add up the cells, then "" will be fine - if,
instead, you are referring to specific cells like =A1 + A2 + A3, then
you will need to change the "" in the middle to 0 (zero).

Hope this helps.

Pete

hotelmasters wrote:
I have a formula in a cell(A) that reads the information from another cell(B)
using the Vlookup function. However, some of these B cells contain a formula
with no results in them, resulting in A displaying the #VALUE! error. is
there a way to ignore those cells that contain no values?

I am also trying to add up all of the A cells but obviously get the #VALUE!
as a result because of the previous errors. Anyway to count these VALUE
cells as zero or circumvent in any way?

thanks

You shouldn't get the #VALUE error in any of the cells with your lookup
formula - you will get "" or 0 instead - so that means you should not
get the error when you try to sum the cells.

Hope this helps.

Pete

hotelmasters wrote:
thanks for the help - what about the second part - can i ignore the value
error when adding them together with a formula of =sum('Q1:Q4'!D9)?


"Pete_UK" wrote:

Try this:

=IF(ISERROR(your_formula),"",your_formula)

If you are using SUM to add up the cells, then "" will be fine - if,
instead, you are referring to specific cells like =A1 + A2 + A3, then
you will need to change the "" in the middle to 0 (zero).

Hope this helps.

Pete

hotelmasters wrote:
I have a formula in a cell(A) that reads the information from another cell(B)
using the Vlookup function. However, some of these B cells contain a formula
with no results in them, resulting in A displaying the #VALUE! error. is
there a way to ignore those cells that contain no values?

I am also trying to add up all of the A cells but obviously get the #VALUE!
as a result because of the previous errors. Anyway to count these VALUE
cells as zero or circumvent in any way?

thanks