Modifying equations that use INDIRECT() inside ARRAYFORMULA()

From what I have read, it appears that INDIRECT() does not function with ARRAYFORMULA(), however have seen some custom workaround solutions for specific situations. Could anyone assist me with modifying the following equations such that I can use ARRAYFORMULA() with them?

Equation 1:

=IF($I$11<>"", SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$M$5&"6:"&$M$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31)), SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$P$5&"6:"&$P$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31)))

Equation 2: =IFERROR(IFNA(SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$M$5&"6:"&$M$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31))/O2,"-"),"-")

Equation 3: =SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$P$5&"6:"&$P$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31))

Appreciate any help I can get with understanding how to modify these!

Edit: Taking Erik's suggestion of not requiring INDIRECT(), I have managed to reference the same set of data using OFFSET() with the following:

=ARRAYFORMULA(IF($I$11:I11<>"", SUMIFS(OFFSET('Earnings Pivot Statement'!$A$6, 0, $M$2-1, COUNTA('Earnings Pivot Statement'!$A$6:A)+COUNTBLANK('Earnings Pivot Statement'!$A$6:A),1),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2:A,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2:A,12,31)), SUMIFS(OFFSET('Earnings Pivot Statement'!$A$6, 0, $P$2-1, COUNTA('Earnings Pivot Statement'!$A$6:A)+COUNTBLANK('Earnings Pivot Statement'!$A$6:A),1),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2:A,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2:A,12,31))))

However, ARRAYFORMULA() is only populating 1 cell currently, not sure why

Solution

You are looking up data from a pivot table, which seems like an unnecessary step. Try something like this to get the total directly from 'Earnings Input':

=query( 
  'Earnings Input'!A1:G, 
  "select year(A), sum(G) 
   where B is not null 
   group by year(A) 
   label year(A) 'Year', sum(G) 'Total' ", 
  1 
)

=arrayformula( 
  iferror( 
    vlookup( 
      G18:G, 
      query( 
        'Earnings Input'!A1:G, 
        "select year(A), sum(G) 
         where B " & if(len(I11), "= '" & I11 & "' ", "is not null") & "
         group by year(A)", 
        1 
      ), 
      2, false 
    ) 
  ) 
)

These formulas do not require any helper columns. Cell I11 is a drop-down list that lets you select a name to filter by, or leave empty to show all.

Additional data points can be obtained like this:

=arrayformula( if( not(len(G5)) * len(F9:F), "-", iferror( F9:F / E9:E ) ) )

=arrayformula( 
  if( 
    not(len(G5)) * len(F9:F), 
    "-", 
    iferror( 100% - G9:G / isnumber(G9:G) ) 
  ) 
)

=if( 
  len(G5), 
  G5 & " % (" & to_text(round(average(G9:G), 2)) & ")", 
  "No filter" 
)

See the new Solution3 sheet.

The solution above is still a bit verbose, primarily because of the report format you have chosen. It is usually easier to display the data for all categories at once. To get an idea what that would look like, try this:

=query( 
  'Earnings Input'!A1:G, 
  "select year(A), sum(G) 
   where B is not null 
   group by year(A) 
   pivot B 
   label year(A) 'Year', sum(G) 'Total' ", 
  1 
)

=arrayformula( 
  if( 
    isnumber(B3:J15) * isnumber(B3:B15) * isnumber(B3:J3), 
    B3:J15 / B3:B15, 
    B3:J15 
  ) 
)

See the new Solution2 sheet.