If, Vlookup & Sum

• Hi all, Im a newcomer to the site, have been in the background for a while and have got some great help and tips from this forum, but now I have a question that I can't find an answer to.

I have 2 worksheets, 1 & 2.
Number 1 has a list of clients i.e tom, john, paul and each client has several different values at different dates i.e
Tom - 15
Tom - 20
John - 30
John - 35 etc.

On worksheet 2 I have the full list of clients in the first column and on the second column I need to have the total for each client from worksheet number 1.
Can anyone advice a formula I could use in worksheet 2 that will lookup the client name in cell A1 and return the total for that client from worksheet 1?
Many thanks.

• Re: If, Vlookup &amp; Sum

Welcome to the forum!

Use Sumif for something like this -

=SUMIF(Sheet1!A1:B4,Sheet2!A1,Sheet1!B1:B4)

This assumes your sample data above is in A1 to B4 (i.e. on sheet 1, 4 names in Column A with 4 values in column B). Also assumes that "Tom" is listed in A1 of sheet 2.

Put that formula into Sheet2, cell B1.

Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: If, Vlookup &amp; Sum

This is a very similar solution to Ger's, but it uses SUMPRODUCT and assumes a header row in row 1 of each sheet.[f]=SUMPRODUCT((Sheet1!\$A\$2:\$A\$2000=Sheet2!A2)*(Sheet1!\$B\$2:\$B\$2000))[/f]

See the attached.

And ... Welcome to OzGrid

Files

• Re: If, Vlookup &amp; Sum

Hi

Welcome to the forum

enter this formula in the cell on worksheet2 next to the client name

=SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)

where A1:A12 is the range of clints and C1:C12 is the range containing the various values

HTH

Robert

ps sorry to appear to have duplicated Ger's solution, my phone rang before I could send

R

• Re: If, Vlookup &amp; Sum

Hi

Welcome to the forum

enter this formula in the cell on worksheet2 next to the client name

=SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)

where A1:A12 is the range of clints and C1:C12 is the range containing the various values

HTH

Robert

• Re: If, Vlookup &amp; Sum

though you may find this variation easier to copy down:
[frc]=SUMIF(Sheet1!\$A\$1:\$B\$4,Sheet2!A1,Sheet1!\$B\$1:\$B\$4)[/frc]
just adds absolute refs where appropriate.

p45cal

• Re: If, Vlookup &amp; Sum

Good call P45Cal.

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: If, Vlookup &amp; Sum

Now thats what you call service!! Fantastic work guys, thank you very much!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!