Add up all numbers in VLOOKUP that has identical lookup value

[ssolomon via OfficeKB.com]

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

 

[Don Guillett]

Look in the help index for SUMIF.

 

[Guest]

Take a look SUMIF function in Help menu

 

[T. Valko]

Try this:

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

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

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

 

[ssolomon via OfficeKB.com]

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

Try this:

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

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

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

Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers

[quoted text clipped - 17 lines]

Steve

 

[T. Valko]

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

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

However, if you have:

Smith...Brandon
Smith...Joe

Then SUMIF will sum *every* Smith.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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

Try this:

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

Better if you use cells to hold the criteria:

A1 = Smith
B1 = Brandon

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

Maybe VLOOKUP is not the best function to use; but I want to pull ALL
numbers

[quoted text clipped - 17 lines]

Steve