Excel INDIRECT formula nested within SUMPRODUCT returning zero values

Dan O 40

Have workbook with multiple tabs for each week, want to sum total $'s billed by Client / Subcontractor.
Set up Table named "Weeks" with "Week Number" column referencing each tab.
Formula shown below, where Column 'c' references $'s Billed, Column 'a' references Client, and Column 'e' references Subcontractor.
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Weeks[Week Number]&"'!c:c"),INDIRECT("'"&Weeks[Week Number]&"'!a:a"),$A21,INDIRECT("'"&Weeks[Week Number]&"'!e:e"),$E21)),)

Ashish Mathur 102.5K Reputation points Volunteer Moderator

2026-05-27T23:22:21.93+00:00

Hi,

Please share the download link of the file with your formulas already there.
Dan O 40 Reputation points

2026-05-28T00:01:22.4166667+00:00

Ashish - see link
https://1drv.ms/x/c/283357e4b36b4309/IQCjcqfvhDe9RaXrKtKRTNKWAYF3EP_IiUXosn9CvZyqZg8?e=Ho2cRP
Dan O 40 Reputation points

2026-05-28T13:26:07.19+00:00

Ashish - resending link with original formulas.

https://1drv.ms/x/c/283357e4b36b4309/IQDZs_RuVq9IRaBJzcaNkrjSAV28o2ILTXsXsGDnHEfZCu8?e=0FRN5E

Answer accepted by question author

Ashish Mathur 102.5K Volunteer Moderator

Hi,

In range J2:J4 of the Recap worksheet, type Week23, Week24 and Week25. In cell C2, enter this formula and drag down

=SUMPRODUCT(SUMIF(INDIRECT("'"&$J$2:$J$4&"'!A2:A100"),A2,INDIRECT("'"&$J$2:$J$4&"'!C2:C100")))

Hope this helps.

User's image

Dan O 40 Reputation points

2026-05-29T01:01:41.95+00:00

Ashish - thank you - that enables me to summarize without specifying each worksheet name in a long drawn-out formula. I noticed you are using the SUMIF function; can this approach work using SUMIFS function so that we can get Subcontractor sub-totals within Client totals?

(While this test file has a one-to-one relationship of Subcontractors to Clients, the live data may have multiple subcontractors for a given client.)

Regards,

Dan O
Ashish Mathur 102.5K Reputation points Volunteer Moderator

2026-05-29T02:48:52.7866667+00:00

You are welcome. If my previous reply helped, please mark that as Answer. For your new question, share relevant data and show the expected result.

Answer accepted by question author

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Dan O,

Based on your sharing, it's because that the IFERROR() is hiding the error result and return "0" when the inner formula is actually returning #VALUE! or #REF!.

Therefore, to debug this issue, I suggest you should temporarily remove IFERROR and see what is the actual result that the inner formula returns. Please share me the formula outcome and if possible, some screenshots of the data table structure so I can better understand about the situation and provide the best possible assistance for your concern.

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I look forward to hearing from you.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Dan O 40 Reputation points

2026-05-27T19:01:12.02+00:00

Hendrix - your observation was correct - removing the IFERROR condition produces a #REF! result.

The revised formula is now:
=SUMPRODUCT(SUMIFS(INDIRECT("'" & Weeks[Week Number] & "'!c2:c100"),INDIRECT("'" & Weeks[Week Number] & "'!a2:a100"), $A2,INDIRECT("'" & Weeks[Week Number] & "'!e2:e100"), $E2))

Attached are screenshots of:
Weeks Table
Recap Tab containing formulas

Week23 Tab showing test data

Regards,
Dan O

Table_Weeks.JPG

Recap Tab.JPG
Week23 Tab.JPG
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-27T23:03:18.9733333+00:00
Hi @Dan O,

Thank you for your update information.

The problem of your formula is because of the INDIRECT function. The problem is in this part (INDIRECT("'" & Weeks[Week Number] & "'!c2:c100"). Since Weeks[Week Number] is a valid structured reference, INDIRECT resolves that one reference and spills the values:

Week19 Week20 Week21 ....

Then Excel will build the texts in C2:C100 internally for each reference value:

'Week19'!C2:C100 'Week20'!C2:C100 'Week21'!C2:C100 ....

But the problem is that INDIRECT cannot return multiple multi-cells ranges as one clean spilled result. For each item, it tries to return a range like C2:C100, not one single value. That creates a “range inside each array item” problem.

You can try running the formula =INDIRECT("'" & Weeks[Week Number] & "'!c2:c100") for testing and you will likely get a result like this

The workaround I found out is to explicitly list the week names so Excel will treat it as a literal array so it can work best with INDIRECT without any problem =SUMPRODUCT(SUMIFS(INDIRECT("'" & {"Week23","Week24"} & "'!c2:c100"),INDIRECT("'" & {"Week23","Week24"} & "'!a2:a100"), $A2,INDIRECT("'" & {"Week23","Week24"} & "'!e2:e100"), $E2))

Hope my sharing may help you clearly understand the situation. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.
Dan O 40 Reputation points

2026-05-28T00:30:39.2033333+00:00

Hendrix - thanks for zeroing in on that issue. Specifying each worksheet does produce correct results; but I had hoped to avoid having to list each of the 52 weekly tab names in the formulas. I came across an old article that indicated the use of a Table might be a workaround to that: https://www.microsoft.com/en-us/microsoft-365/blog/2013/05/01/summing-data-across-multiple-criteria-on-multiple-worksheets/

In any case, I can make do with your solution; but appreciate if you have any further insights regarding the use of tables to streamline the formulas.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-28T21:44:05.6866667+00:00

Hi @Dan O,

I have tried to use the tables or as least ranges to calculate the formula dynamically across multiple sheets/tabs, but it seems like that the tab names need to be listed explicitly for Excel to calculate properly. That's the only workaround I can come up with.

For better possible assistance, you can try the workarounds suggested by other users and moderators or you can share your situation in the Excel | Microsoft Community Hub. This forum is dedicated to Excel and is actively monitored by power users, Microsoft partners, and engineers who can provide step-by-step guidance and share best practices.

I apologize for redirecting you to another community, as I may not have the necessary skills and experience to solve your Excel issue in the most effective way.

Thank you for your patience and understanding during this process. For any questions or further assistance in the future, please feel free to reach out again at any time. Not only will I be happy to assist you, but also all the moderators of the Q&A forum will do their best to provide support and guidance.

I truly hope your concern would be resolved soon. Wishing you a great day ahead.
Dan O 40 Reputation points

2026-05-29T00:42:30.7333333+00:00

Hendrix - no apologies necessary - you're given me a workable approach and I appreciate your suggestions.

1 additional answer

IlirU 2,651 Reputation points Volunteer Moderator

2026-05-28T07:03:01.5366667+00:00
Hi @Dan O

(if I understand correctly what you're looking for)

In cell C2 of the Recap sheet, apply the following formula:
```
=LET(
     weeks, VSTACK(Table2, Week24!A2:H12, Week25!A2:H8),
    Client, TRIM(CHOOSECOLS(weeks, 1)),
inv_Client, TRIM(CHOOSECOLS(weeks, 3)),
       Sub, TRIM(CHOOSECOLS(weeks, 5)),
            BYROW(A2:A5 & E2:E5, LAMBDA(a, SUM(
                  --FILTER(inv_Client, Client & Sub = TRIM(a))))
                  )
)
```
In cell F2 of the Recap sheet, apply the following formula:
```
=LET(
     weeks, VSTACK(Table2, Week24!A2:H12, Week25!A2:H8),
    Client, TRIM(CHOOSECOLS(weeks, 1)),
   inv_Sub, TRIM(CHOOSECOLS(weeks, 6)),
       Sub, TRIM(CHOOSECOLS(weeks, 5)),
            BYROW(A2:A5 & E2:E5, LAMBDA(a, SUM(
                  --FILTER(inv_Sub, Client & Sub = TRIM(a))))
                  )
)
```
HTH

IlirU
Was this answer helpful?
Dan O 40 Reputation points

2026-05-28T13:15:27.6866667+00:00

IlirU - thank you for the alternative approach. It appears that it would still require specifying each weekly tab name in the formulas, and calculation time is longer; but I will give it further consideration.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Excel INDIRECT formula nested within SUMPRODUCT returning zero values

1 additional answer

Your answer