How to sum up many vlookups?

[danix]

Here is a sample image if the setup:
https://i.imgur.com/m6NDab9_d.webp?maxwidth=760&fidelity=grand

I have a known list of items (name1, name2…) that each have a, b, c, d, e values. Then I have a list of items that has name1 and name2. This list is added manually and could be 2 names, 1 name or 50 names. List is B14:B15 but is actually like B1:B120…

I want now the sum of the a, b, c, d and e values of the listed items (name1 and name2) in that last “totals” cells. So now it would be:
a 5+2=7
b 50
c 12
d 8
e 11

Naturally the file has hundreds of names and multiple as and bs and cs so I need a smarter way to count the sum than just typing out the hundreds of vlookups as in vlookup(B14;…)+vlookup(B15…)+…

here is a sample file:
sums.ods (11.9 KB)

new sample file with dirty doubles:
sums.ods (38.5 KB)

issue I found:
sums.ods (38.5 KB)

[JohnSUN]

Yes, you can do it without VLOOKUP().

One of the many possible options is a formula like

=SUMPRODUCT(OFFSET(<range_of_Names_column>;0;MATCH(<cell_with_value_to_sum>;<row_with_values>;1));ISNUMBER(MATCH(<range_of_Names_column>;<range_with_List_of_Names>;0)))

It is a pity that you did not give a sample of data, but only a screenshot (the column headings and line numbers are not visible there) - the formula could be shown with reference to your coordinates, it would be clearer.

Despite the cumbersome writing, the formula is actually very simple.

First, we find how far from the column with names is the column for the corresponding value
MATCH(<cell_with_value_to_sum>;<row_with_values>;1)

Then cuts out from all data only the column for the corresponding value

OFFSET(<range_of_Names_column>;0;<prev_index>)
Now multiply by zero all values in rows in which names are not included in the list (or 1 if included in the list)

ISNUMBER(MATCH(<range_of_Names_column>;<range_with_List_of_Names>;0))

SUMPRODUCT() will find the required amount

Remember to use absolute addresses when specifying ranges

For my test data the formula is working correctly
=SUMPRODUCT(OFFSET($B$3:$B$9;0;MATCH(B24;$C$2:$G$2;1));ISNUMBER(MATCH($B$3:$B$9;$B$17:$B$18;0)))

Update Your sheet with formulas - sums.ods (41.9 KB)

[danix]

Thanks for the solution. I don’t quite understand how it works. I added a sample file to the opening post.

[JohnSUN]

@danix I added a sample file to the my answer

[danix]

Your solution works fine as long as there are no duplicates in the list. For example if I have multiple name1 items in the list the solution you gave only counts it once. Some of the sums also don’t come out quite right. I have some missing rows that maybe mess it up…

[JohnSUN]

@danix

there are no duplicates in the list

OMG! In your question, you did not mention that data can be “dirty”.

Where is “Name1” duplicated? In the original table or in the list? If it is repeated, what should be summed up? Can you prepare another sample data?

[danix]

Sorry, totally my fault. I’ll update the sample file asap!

[JohnSUN]

So?
sums (2).ods (13.9 KB)