# How to combine ARRAYFORUMLA with INDIRECT to iterate over multiple sheets?

In Google Sheets I have employees' expense data.

Each sheet contains data for multiple employees, for a single month.

In a main sheet I have a formula that returns the SUM of all expenses for a single employee across all months (sheets).

# Current Formula

``````=SUM(
IFNA(filter('Oct. Expenses'!\$D\$4:\$D\$100, regexmatch('Oct. Expenses'!\$C\$4:\$C\$100, F9)),0),
IFNA(filter('Nov. Expenses'!\$D\$4:\$D\$100, regexmatch('Nov. Expenses'!\$C\$4:\$C\$100, F9)),0),
IFNA(filter('Dec. Expenses'!\$D\$4:\$D\$100, regexmatch('Dec. Expenses'!\$C\$4:\$C\$100, F9)),0),
IFNA(filter('Jan. Expenses'!\$D\$4:\$D\$100, regexmatch('Jan. Expenses'!\$C\$4:\$C\$100, F9)),0),
IFNA(filter('Feb. Expenses'!\$D\$4:\$D\$100, regexmatch('Feb. Expenses'!\$C\$4:\$C\$100, F9)),0))
``````

This formula and variations are used in multiple places. The current approach works but is cumbersome because each time a new monthly sheet is added, all formulas must be manually edited to include it.

I want to make this formula dynamic by listing the sheet names in column `L` and passing them to the formula. In that way I can simply add a new sheet name to that column and all the formulas will update accordingly.

I tried the following formula but it only works for a single sheet name, not the list of sheets in `L1:12`.

What am I missing?

``````=SUM(
ARRAYFORMULA(
FILTER(
INDIRECT(L1&"!\$D\$4:\$D\$100"),
REGEXMATCH(INDIRECT(L1&"!\$C\$4:\$C\$100"), \$F\$9))))
``````

# Sample Monthly Expenses Sheet

A B
3 Employee Amount
4 Doe A'Deer \$5. 28
5 Down Town \$5. 39
6 Sally Field \$4. 02
7 GI Joe \$12. 00
8 Doe A'Deer \$8. 59
9 GI Joe \$14. 67
10 Doe A'Deer \$9. 97
11 Down Town \$10. 87
12 Jane Doe \$10. 68
13 Down Town \$9. 06
14 GI Joe \$8. 98
15 Doe A'Deer \$13. 31
16 Down Town \$12. 24
• Welcome to Web Applications Stack Exchange. You may want to try the stackValues_ pattern. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing an editable sample spreadsheet. Commented Oct 2, 2023 at 17:15
• You should identify the data type required by each function parameter before working with complex formulas. Some functions allow parameters that could be values or cell / range references, but others only accept range references. Also, some types of data should be scalar (a single value), others might be single or arrays (multiple values), and others should be arrays. Commented Oct 2, 2023 at 17:19

# QUERY

This approach uses QUERY instead of FILTER

``````=SUM(MAP(L1:L12, LAMBDA(r, TOROW(
QUERY(INDIRECT("'"&r&"'!C4:D100"),
"select D where C matches '"&F9&"'")))))
``````

OR use Contains (see explanation below)

``````=SUM(MAP(L1:L12, LAMBDA(r, TOROW(
QUERY(INDIRECT("'"&r&"'!C4:D100"),
"select D where C contains '"&F9&"'"),2))))
``````

# NOTES

##### BYROW vs. MAP
• The BYROW function achieves the same goal in this use case, however MAP was used in the formula simply because it contains fewer characters.
##### Absolute Reference as a String
• `C4:D100` can be written as absolute or relative because it is a text string, not a cell reference, therefore the references can never change unless you manually edit them. This also applies to your original formula that used text strings. I chose the relative version as it contains fewer characters, and doing otherwise has no benefit.
##### Sheet Names Range
• Your range containing the sheet names, specified as `L1:L12` in your question, can include empty rows without breaking the formula.
• For example, if you have sheet names in `L2:L5` your range could be `L1:L25` or even `L:L`.
• If you plan to copy the formula to other rows and/or columns then the range row and/or column references should be absolute `L\$1:L\$12` or `\$L1:\$L12` or `\$L\$1:\$L\$12` or `\$L:\$L` etc.
##### Matches (Regular Expressions)
• In keeping with your own use of the REGEXMATCH function, and without knowing what is in `F9`, I used Matches which performs a regular expression match.
• Your values in `F9` would ned to be something like the following.
• `F9=.*Alice.*` to find "Alice" anywhere in a string
• `F9=.*(Alice|Bob).*` to find "Alice" or "Bob"
• To perform a case-insensitive search, force the range values to uppercase or lowercase and then match that case in your regex.
Two Case Insensitive Examples
``````"select D where lower(C) matches '.*alice.*'"
"select D where upper(C) matches '.*ALICE.*'"
``````
##### Contains
• if you are simply looking for a particular name you could use Contains instead of Matches in the Where clause. Then `F9` would be as follows:
• `F9=Alice` to find "Alice" anywhere in a string
Two Case Insensitive Examples
``````"select D where lower(C) contains 'alice'"
"select D where upper(C) contains 'ALICE'"
``````

I think perhaps you want map/lambda instead of arrayformula.

Try something like this:

``````=sum(map(filter(L:L, L:L<>""), lambda(col, SUM(IFNA(filter(INDIRECT(col&"!\$D\$4:\$D\$100"), regexmatch(INDIRECT(col&"!\$C\$4:\$C\$100"), \$F\$9)))))))
``````