chris9277

Board Regular
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>

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

duggie33

Board Regular
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

Board Regular
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

Board Regular
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

Replies
7
Views
526
Replies
11
Views
604
Replies
1
Views
235
Replies
5
Views
430
Replies
3
Views
228