Add up all numbers in VLOOKUP that has identical lookup value
 

Maybe VLOOKUP is not the best function to use; but I want to pull ALL numbers
associated with a person's name and have them added to one cell.

Example:

A B C D
1 LName FName Dept Hours
2 Smith Brandon 2114 85
3 Smith Brandon 0277 110

The above shows what the data would look like on the sheet entitled JAN. I
want the data on another page, so the cell would contain all the hours that
Brandon Smith worked in one month. Not sure VLOOKUP is the correct solution.

Thanks for any help that can be provided.

Steve

--
Message posted via http://www.officekb.com

Add up all numbers in VLOOKUP that has identical lookup value
 

Look in the help index for SUMIF.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ssolomon via OfficeKB.com" <u39090@uwe wrote in message
news:7b8f49fc2d909@uwe...
Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers
associated with a person's name and have them added to one cell.

Example:

A B C D
1 LName FName Dept Hours
2 Smith Brandon 2114 85
3 Smith Brandon 0277 110

The above shows what the data would look like on the sheet entitled JAN.
I
want the data on another page, so the cell would contain all the hours
that
Brandon Smith worked in one month. Not sure VLOOKUP is the correct
solution.

Thanks for any help that can be provided.

Steve

--
Message posted via http://www.officekb.com

Add up all numbers in VLOOKUP that has identical lookup value
 

Take a look SUMIF function in Help menu


"ssolomon via OfficeKB.com" wrote:

Maybe VLOOKUP is not the best function to use; but I want to pull ALL numbers
associated with a person's name and have them added to one cell.

Example:

A B C D
1 LName FName Dept Hours
2 Smith Brandon 2114 85
3 Smith Brandon 0277 110

The above shows what the data would look like on the sheet entitled JAN. I
want the data on another page, so the cell would contain all the hours that
Brandon Smith worked in one month. Not sure VLOOKUP is the correct solution.

Thanks for any help that can be provided.

Steve

--
Message posted via http://www.officekb.com

Add up all numbers in VLOOKUP that has identical lookup value
 

Try this:

=SUMPRODUCT(--(Jan!A2:A10="Smith"),--(Jan!B2:B10="Brandon"),Jan!D2:D10)

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

=SUMPRODUCT(--(Jan!A2:A10=A1),--(Jan!B2:B10=B1),Jan!D2:D10)

--
Biff
Microsoft Excel MVP


"ssolomon via OfficeKB.com" <u39090@uwe wrote in message
news:7b8f49fc2d909@uwe...
Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers
associated with a person's name and have them added to one cell.

Example:

A B C D
1 LName FName Dept Hours
2 Smith Brandon 2114 85
3 Smith Brandon 0277 110

The above shows what the data would look like on the sheet entitled JAN.
I
want the data on another page, so the cell would contain all the hours
that
Brandon Smith worked in one month. Not sure VLOOKUP is the correct
solution.

Thanks for any help that can be provided.

Steve

--
Message posted via http://www.officekb.com

Add up all numbers in VLOOKUP that has identical lookup value
 

Valko -

Thanks!! I was looking at the SUMIF in the help and was still having
problems. I put this in and it worked perfectly.

Thanks a bunch!!

Steve

T. Valko wrote:
Try this:

=SUMPRODUCT(--(Jan!A2:A10="Smith"),--(Jan!B2:B10="Brandon"),Jan!D2:D10)

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

=SUMPRODUCT(--(Jan!A2:A10=A1),--(Jan!B2:B10=B1),Jan!D2:D10)

Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers
[quoted text clipped - 17 lines]

Steve

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

Add up all numbers in VLOOKUP that has identical lookup value
 

If evey last name was unique (distinct) then you could use SUMIF:

=SUMIF(Jan!A2:A10,"Smith",Jan!D2:D10)

However, if you have:

Smith...Brandon
Smith...Joe

Then SUMIF will sum *every* Smith.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ssolomon via OfficeKB.com" <u39090@uwe wrote in message
news:7b90166c85320@uwe...
Valko -

Thanks!! I was looking at the SUMIF in the help and was still having
problems. I put this in and it worked perfectly.

Thanks a bunch!!

Steve

T. Valko wrote:
Try this:

=SUMPRODUCT(--(Jan!A2:A10="Smith"),--(Jan!B2:B10="Brandon"),Jan!D2:D10)

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

=SUMPRODUCT(--(Jan!A2:A10=A1),--(Jan!B2:B10=B1),Jan!D2:D10)

Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers
[quoted text clipped - 17 lines]

Steve

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1