Formula using sumifs and indirect function for worksheets. Getting Reference error although Had it working with different columns

Question

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.

Answer 1

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.

Answer 2

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.