How do i Summarize values from multiple sheets using vlookup combined with...?

Hi,

I have a workbook with several sheets and on EACH sheet there is a value connected to a name. So i want to create a formula that summarizes all the different values for that spesific name. Again; This name is on all sheets, but different location.

I have made a formula with vlookup that searches all my sheets for a value connected to my lookup_value (name). But i am struggeling to make excel summarize the values.

This is my current vlookup formula: {=VLOOKUP(C2,INDIRECT("'"&INDEX(A19:A22,MATCH(TRUE,COUNTIF(INDIRECT("'"&A19:A22&"'!B6:B16"),C2)>0,0))&"'!B6:F16"),5,FALSE)}
Its searching in the sheets that ive named in cells A19-A22 for the value(name), C2, in their respective cells B6-B16. Then it returns the value from the 5th column in the array B6-F16. This works well, but i want to summarize all the values it would find. Do i have to change my vlookup or can i combine it with something?

I tried playing with sum and sumproduct, but i think im doing something wrong. Still just getting the first value it finds.
Anyone got a clue?

Thanks

Hi,

Attach a workbook with a small, mocked-up example together with your expected results.

Regards

Attached is a simple workbook with 3 sheets with data and 1 sheet where the sum for each "name" is displayed.

Note that the names have different locations and values on each sheet. I want a function which gives me the summarized value for each name.

Hope this explains better

Thanks for all help.

edit 1: In my original workbook i use a column to list the names of all the sheets i want to search in. But im open for suggestions. The orignial workbook also has alot of data, not just three rows as illustrated.

Using your file and putting the list of sheet names in A19:A21 of the Sum sheet, in B7 of that same sheet:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A$19:A$21&"'!A7:A9"),A7,INDIRECT("'"&A$19:A$21&"'!B7:B9")))

and copied down.

Regards

Works well! Thanks alot man! Much simpler than what i was doing.

For those who wonder, if any; This formula searches the sheets named in A19:A21, in Cells A7:A9 for the parametre (in same sheet as formula) A7 and then gives back the value found in B7:B9 from sheets listed in A19:21, also matched with the A7 parametre.

You're welcome!

Cheers