vlookup name & return total value from other column

[chris9277]

Hi,

I have a master sheet with names on but I would like this to lookup the exact name which will appear numerous times on various sheets in column A&B & return any total figures that are in column D, this is all in the same workbook.

This is what the formula will be looking at across all sheets

Picked By	Round	Total Units	Target Rate by Round	Start	Finish	Sched Time (mins)	Actual Mins
Waleed Munawar		A	852	28			30
		B	278	28			10
Alex Ashton		D	241	28			9
Ethan Tye	Waleed Munawar	E	359	28			13
Alex Ashton		F	1053	28			38
Ethan Tye	Leah Curwen	G	54	28			2
		P	0	28			0
		T	615	28			22
		GREENS	785	28			28

<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;" span="2"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" span="5"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <tbody> </tbody>

Many thanks I advance

 

[duggie33]

Hi chris9277,

Try something like this --> =SUMPRODUCT((A1:A5=M1)+(B1:B5=M1),D1:D5)

In this case M1 would contain the name being evaluated. You will have to adjust ranges and apply to multiple sheets.

Doug

 

[chris9277]

Hi Doug,

Thank you for your reply that formula has done the trick regards totalling up, but is there anyway that the formula could look through all the sheets in the same workbook?

Many thanks,

Chris

 

[duggie33]

No problem Chris.

Repeat for other sheets something like this... =SUMPRODUCT((Sheet1!A1:A5=Sheet1!M1)+(Sheet1!B1:B5=Sheet1!M1),Sheet1!D1:D5)+SUMPRODUCT((Sheet2!A1:A5=Sheet1!M1)+(Sheet2!B1:B5=Sheet1!M1),Sheet2!D1:D5).

There is probably a more elegant way to do this that I have not remembered or learned yet. Are the ranges the same on the other sheets?

Doug