Count multiple names, combined with VLOOKUP

[lawrence24]

Hello!

This is some sample data:

Sheet 1:

Name	Total
Jackson
Amy
Meghan

<tbody>
</tbody>

Sheet 2:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
Oranges	XTPC	2	0	1	Honda	Jackson
Apples	PPTC	1	1	1	Toyota	Jackson
Pineapples	LTPC	1	2	0	Mazda	Amy
Bananas	YRGH	6	0	0	Audi	Meghan

<tbody>
</tbody>

What I'm trying to do is calculate the total values in Header 3, 4 & 5 for each name.

So the table in Sheet 1 should be:

Name	Total
Jackson	6
Amy	3
Meghan	6

<tbody>
</tbody>

I can't figure out how to loop over VLOOKUP through the sheet, to match all instances of the names in Sheet 1!

Please help!

 

[steve the fish]

Use a SUMPRODUCT:

=SUMPRODUCT(Sheet2!$C$2:$E$5*(Sheet2!$G$2:$G$5=Sheet1!A2))

 

[lawrence24]

That works! But one thing - I don't always know how big the table is going to be vertically. It could be G40, it could be G90. How do I account for this?

 

[RossTattersall]

I would establish it as a dynamic named range.

 

[lawrence24]

Hi! Thanks for the suggestions - but please could you tell me how to? I'm not any good with this stuff!

 

[steve the fish]

Just make it big enough to always be big enough. Say go down to row 1000 with the formula. That assumes there is no data below the table.