Vlookup Encounters a call with no data and returns #N/A
You have to change each =vlookup() in your formula so that it checks for an
error. If it's an error, return a 0.
=if(isna(VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)) ,0,
VLOOKUP($A2,December!$C$3:$H$68,3,FALSE))
+if(isna(VLOOKUP($A2,January!$C$3:$H$65,3,FALSE)), 0,
VLOOKUP($A2,January!$C$3:$H$65,3,FALSE))
+ ...
If that doesn't work, post the formula that you tried.
Six Sigma Blackbelt wrote:
Dave Thanks, that help, but when a persons name isnt in the sheet it returns
a 0 and dosent continue to add the cells in the multiple sheets. So basically
i need the formula to skip that sheet if the names dosent appear in the sheet
and move on to the next sheet, sum the cell with other cells that is has
already sum and continue the process until all sheets have been sum.
"Dave Peterson" wrote:
=VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)
+VLOOKUP($A2,January!$C$3:$H$65,3,FALSE)
+VLOOKUP($A2,February!$C$3:$H$74,3,FALSE)
+VLOOKUP($A2,March!$C$3:$H$85,3,FALSE)
+VLOOKUP($A2,April!$C$3:$H$85,3,FALSE)
You could check to see if each addend is an error. If it is, then return 0:
=if(isna(VLOOKUP(...)),0,vlookup(...))
+if(isna(VLOOKUP(...)),0,vlookup(...))
....
Six Sigma Blackbelt wrote:
My vlookup formula goes like this
=VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)+VLOOKUP( $A2,January!$C$3:$H$65,3,FALSE)+VLOOKUP($A2,Februa ry!$C$3:$H$74,3,FALSE)+VLOOKUP($A2,March!$C$3:$H$8 5,3,FALSE)+VLOOKUP($A2,April!$C$3:$H$85,3,FALSE)
The formula finds a name an adds the numbers associated with that name in
the cells together, but some of the months like April do not have the names
of the people so the vlookup returns #N/A instead of the sum, because that
name could not be found in the specified tab labeled March or April. What
kind of IF Statement can i use to continue adding the cells even if that
persons name dosen't show up in that tab.
--
Dave Peterson
--
Dave Peterson
|