Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
Hello:
I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. Thanks. Art |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
instead of =VLOOKUP()
use: =IF(ISERROR(VLOOKUP()),0,VLOOKUP()) -- Gary''s Student - gsnu200854 "art" wrote: Hello: I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. Thanks. Art |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
Thanks. But is there a way to just check if the cell is empty then dont do
anything. Because Your way, I have to enter the formula Vlookup twice (if vlookup is error then zero, if not the vlookup). Is there a way to do this by entering only once the vlookup formula? Thanks Art "Gary''s Student" wrote: instead of =VLOOKUP() use: =IF(ISERROR(VLOOKUP()),0,VLOOKUP()) -- Gary''s Student - gsnu200854 "art" wrote: Hello: I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. Thanks. Art |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
One way. Place the 4 vlookups, each in its own cell within a contiguous
range, say in A2:A5. Then you could use this expression in A6 to sum it up, which will ignore the possible errors or text returned by any of the 4 vlookups: =SUM(IF(ISNUMBER(A2:A5),A2:A5)) P/s: I'd think that you should review your vlookups to use an exact match, viz: instead of: vlookup(D1,EmpList,2) use: vlookup(D1,EmpList,2,0) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "art" wrote: I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
=SUM(IF(ISNUMBER(A2:A5),A2:A5))
Missed out mentioning earlier that the expression above needs to be array-entered, ie confirmed via pressing CTRL+SHIFT+ENTER (instead of just ENTER) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
If the first vlookup returns an error, it doesn't calculate anymore, which is
not what I want. If the first vlookup returns anm error, it should go to the next vlookup. Thanks. "Gary''s Student" wrote: instead of =VLOOKUP() use: =IF(ISERROR(VLOOKUP()),0,VLOOKUP()) -- Gary''s Student - gsnu200854 "art" wrote: Hello: I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. Thanks. Art |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup ignoring empty cells
Try this:
=SUMPRODUCT(SUMIF(A1:A4,D1:D4,B1:B4)) -- Biff Microsoft Excel MVP "art" wrote in message ... Hello: I would like to add two amount by using the vlookup, however one might be empty, which causes the vlookup to return #Value error. How can I check if one cell is empty then dont add that cell. Lets say : A B C D 1 John 600 Mark 2 Mark 550 Jones 3 Marry 650 4 Jones 645 And in cell A6 I should have the formula =(vlookup(D1,EmpList,2)+Vlookup(D2,EmpList,2)+Vloo kup(D3,EmpList,2)+Vlookup(D4,EmpList,2) It should return the total amount and ignoring the cell that are empty. Thanks. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring empty field in sum | Excel Worksheet Functions | |||
Excel - Geomean - Ignoring Empty Cells | Excel Worksheet Functions | |||
Ignoring empty cells | Excel Worksheet Functions | |||
empty cells in a vlookup list | Excel Worksheet Functions | |||
Ignoring empty cells | Excel Discussion (Misc queries) |