# SumIFS with dynamic sheet reference not using INDIRECT

1. ## SumIFS with dynamic sheet reference not using INDIRECT

i use in my excel table sumifs and countif in conjunction with INDIRECT to summarize data from various sheets. The data is in various sheets because the 30km project is managed in different section for which different people are responsible. so a single data worksheet is not practical
i know that INDIRECT can be replaced by CHOOSE or INDEX however i cannot figure out how it could work in this case.
is there any solution to it or do i need to use VBA?

2. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

Welcome to the forum.

It's important for us to know why you don't want to use INDIRECT. Is it because the workbooks will not be open? Or something else?

Do you have the PowerQuery add-in?

3. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

For the formulas used and the layout of your sheet, INDIRECT is the only practical solution. To use CHOOSE or INDEX in place of INDIRECT means entering each sheet and range into the formula, you would not be able to refer to column A for the sheet name.
If your only reason for it is to tidy the sheet up and clear the errors caused by sheets not yet created, then IFERROR would be the best choice.

4. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

INDIRECT is volatile and thus recalculates every time i make a change.
way too slow

5. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

Have you considered manual calculation, or short circuiting the volatile functions (which is effectively manual calculation of selected formulas)?

edit:- example to short circuit, based on the current formula in C12.

First go to File > Options > Formulas and check the box for 'enable iterative calculation'

Enter the formula into C12,

=IF(LEN(\$N\$1),SUMIFS(INDIRECT(\$A12&"!aa:aa"),INDIRECT(\$A12&"!D:D"),\$B\$4,INDIRECT(\$A12&"!G:G"),C\$6)+SUMIFS(INDIRECT(\$A12&"!ae:ae"),INDIRECT(\$A12&"!D:D"),\$B\$4,INDIRECT(\$A12&"!G:G"),C\$6),C12)

When N1 is empty, the existing value in C12 is shown without recalculation. When N1 is not empty, the full formula is recalculated as volatile, effectively switching calculation on or off for any short circuited formula by simply typing into, or deleting the content of N1.

6. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

Thank you for valuable comments and suggestions.
So I guess I have only 2 choices then, either go for manual recall or rebuild the workbook into a single data sheet
Any other ideas?

7. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

IF the use of indirect is purely to make entry of the formulas easier then maybe create a direct formula using less direct methods.

Try entering this formula into No Anchorages C12, then copy it to C12:I29, K12:Q29,S12:Y29 and AA12:AG29.
Formula:
`Please Login or Register  to view this content.`
Next copy, and paste special > values on the same range.

Finally, use Ctrl h to do a find and replace of = with = (replace all).

The above steps allow you to create a whole formula as a text string, then convert it to a valid formula (with volatile functions removed).

8. ## Re: SumIFS with dynamic sheet reference not using INDIRECT

Hi Jason
thank you for this clever trick, works like a charm

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1