2

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
Improve this question
2
  • 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.
    – doubleunary
    Oct 2 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.
    – Rubén ♦
    Oct 2 at 17:19

2 Answers 2

Reset to default
1

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'"
Improve this answer
1

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)))))))
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.