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

[TABLE="width: 495"]
<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>[TR]
[TD="width: 220, bgcolor: transparent, colspan: 2"]Picked By[/TD]
[TD="width: 62, bgcolor: transparent"]Round[/TD]
[TD="width: 62, bgcolor: transparent"]Total Units[/TD]
[TD="width: 62, bgcolor: transparent"]Target Rate by Round[/TD]
[TD="width: 62, bgcolor: transparent"]Start[/TD]
[TD="width: 62, bgcolor: transparent"]Finish[/TD]
[TD="width: 62, bgcolor: transparent"]Sched Time (mins)[/TD]
[TD="width: 62, bgcolor: transparent"]Actual Mins[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: transparent"]Waleed Munawar[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]852[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]278[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Alex Ashton[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"]241[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ethan Tye[/TD]
[TD="bgcolor: transparent"]Waleed Munawar[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]359[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Alex Ashton[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent"]1053[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]38[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Ethan Tye[/TD]
[TD="bgcolor: transparent"]Leah Curwen[/TD]
[TD="bgcolor: transparent"]G[/TD]
[TD="bgcolor: transparent"]54[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]P[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]T[/TD]
[TD="bgcolor: transparent"]615[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]22[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]GREENS[/TD]
[TD="bgcolor: transparent"]785[/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]28[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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