SUM of Index/Match of multiple worksheets

Hi all, and thank you in advance for your kind reply.

I am wondering if it is possible to SUM the looking up the results of Index Match from multiple worksheets?

I have attached a basic example, and I use Office365.


Some points to note:

* The worksheets I am looking up and then totalling, will not always look the same, or be in the same formatting/order, so a simple 3D sum is not ideal, and thus was hoping to use an index match method, or other method you can recommend. The example attached the cells on each sheet are in different locations. Happy to use Arrays if you can provide some guidelines on how that might work in this example.

* It is a simple workbook with multiple sheetnames, and I wish to sum up index/match onto a cover sheet.

* I understand it can be done manually by INDEX/MATCH + INDEX/MATCH + INDEX/MATCH, however, there will be a large number of sheets, and I was hoping to sum up by a named range listing the worksheets, or to sum up in between worksheets (start/end), or sheet3 to sheet5 etc.


I did try searching for some examples on the forums, but couldn't find anything that matched my request, so any assistance would be greatly appreciated.

From my example.xlsx, Cell F6 on "Summary" sheet is a simple INDEX/MATCH of:
=INDEX(Sheetname1!F:F,MATCH($C6,Sheetname1!$C:$C,0)) + INDEX(SheetnameABC!F:F,MATCH($C6,SheetnameABC!$C:$C,0)) + INDEX(SheetnameB2!F:F,MATCH($C6,SheetnameB2!$C:$C,0))

I was hoping to shorten this formula, it only sums 3 sheets, but it could be 20+ sheets.
The only data in this formula that would change is the worksheet name, otherwise all other data and column locations will be consistent, so I was hoping that sheet name can be dynamic.


Thank you kindly.

Kylie

TRY
worksheet name : Summary
Cell F6 formula , Drag down and accross

HTML Code: 

Why multiple sheets when the format is the same (reference Richard's reply in post#2)?

Hi wk9128, thank you, that did work, and its a lot more elegant that what I had, so appreciate that.

Do you know if I can replace "Sheetname1","SheetnameABC","SheetnameB2" in your formula to some kind of named range? This would shorten this a lot, and then I think that has solved my problem.
Or are you aware if instead of a named range, we could swap that to name all sheets "in between" sheets "Start" and "End" (or in between Sheets2 and Sheets6?

If not further update to your reply, you still made mine much better, so again, appreciate that.

Hi JohnTopley/Richard. Thank you for your reply. The sheets will actually be different, its just in my example they appeared to be the same. I tried to show they were different by moving the rows around on each sheet. Probably should have deleted some fields.

The only constant would be the columns will be consistent on each sheet, but rows and item numbers will vary.

Great suggestion Richard. I had initially thought about a pivot table which would have made my life a bit easier, I am just using this example xlsx to find a way to sum lookups on various sheets, but the final result of the workbook will look completely different. This is just the one formula or task I am a bit stuck on. Without going into all the details, the final sheet (lets call it a cover) will have various versions for different stakeholders, and I am trying to appease the needs of the many as those people arent versed in pivot tables or getting data easily.

If the columns are consistent then I am not sure of the "problem" as rows are (almost) always different values. It is easier to 2select2 from a single sheet rather than combine from multiple sheets

And with INDIRECT you can use a named range containing your sheet names.

I understand the confidentiality issue but it still might worth looking a Richard's suggestion of combining into a "summary" using VBA (and VBA is not always an option because of company policy not to use it).

Enough said!

Originally Posted by wk9128

TRY
worksheet name : Summary
Cell F6 formula , Drag down and accross

HTML Code: 

Hi wk9128, thank you for your reply on the formula. When I replied to this, I forgot to use "reply with quote" option. So I've just redone this.

Is it possible to change the names of the Sheets to a named range, or other method you can recommend (ie sheet names in a cell). If it were a named ranged or other method, could you modify the formula you provided to give me something to play with? This refers to sheet string from your formula - "Sheetname1","SheetnameABC","SheetnameB2"

Thank you. Kylie

The name of your worksheet has been changed to 1, 2, 3, please refer to it
G18 and I18 are changed, there will be statistics for different months