Using excel 365. I have used this same formula for "asset allocations" and it worked perfectly. I decided to do the same for sector allocation and I can not figure out what is wrong. This has beat me down. I have tried for 2 days. I thought that I could just exchange the category for sector. Didn't work. Thank you for any help.

Here is the image which shows the sheet that I am using. The formula in G6 is:

=SUMPRODUCT(SUMIFS(INDIRECT(K$6:K$11&"[Value]"),INDIRECT(K$6:K$11&"[Sectors]"),[@Sector])) You can see a REF Error on the column "Value".

This image is to show where the "Sectors" are listed on the individual sheets that I wish to sum.

CodePudding user response：

Unusual, but this is working fine in my test scenario.

Use the Evaluate Formula tool to see where the error creeps in. You may have a typo in the list of tables.

CodePudding user response：

I should have found the answer earlier but I didn't question my table columns. In my last table the Column was accidentally called "Sector" and it should have been called "Sectors". I needed to pinpoint the point of problem by debugging better. This code does indeed work fine for anyone wishing to sum by a category from more than one sheet. Not saying it is elegant code but it is easy to understand.