Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?
Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?
Embed your vlookup inside an if. Instead of =vlookup(...), use
=if(isna(vlookup(...)),0,vlookup(...))
"Rex" wrote:
> Trying to sum a column that was built using VLOOKUP. Several of the values
> returned in the column are #NA. The column will not sum. How can we write
> the VLOOKUP formula to return error values = to 0 rather than #NA?
You could change the Sum() formula:
=SUMIF(A1:A20,"<>#N/A")
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
"Rex" <[email protected]> wrote in message
news:[email protected]...
Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?
Another way - adjust your VLOOKUP formulae to the following:
=IF(ISNA(VLOOKUP formula),0,VLOOKUP formula)
and copy this down.
Hope this helps.
Pete
General soln is
=IF(ISNA(Your_Formula),0,Your_Formula)
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------
"Rex" <[email protected]> wrote in message
news:[email protected]...
> Trying to sum a column that was built using VLOOKUP. Several of the
> values
> returned in the column are #NA. The column will not sum. How can we
> write
> the VLOOKUP formula to return error values = to 0 rather than #NA?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks