title: Excel VBA SUMIF across multiple sheets
body: <p>I am writing a function to sum flight hours of each day for 1 entire month. </p>
  
  <p>I have tried 2 scenarios - returning a string that would populate the cell with a formula.
  Attempt to return the value for the cell. The second I suppose is preferred.
  The Function should should return either var or string.
  All my attempts have thrown errors on the line assigning the return value.</p>
  
  <pre><code>Option Explicit
  
    Function HoursFlown() As String
    'writes formula for cells d10:d14 to calculate hours flown
    'checks across all worksheets (each day of month) confirms pilot
    'and a flight exists and sums "flight hours totals" for toatal hours in month to date.
    '!!!At present this is not a function so does not return a result and takes no arguments.
       Application.Volatile
       Dim WS_Count As Integer
       Dim I As Integer
       Dim strTabs As String
  
       ' Set WS_Count equal to the number of worksheets in the active
       ' workbook.
       WS_Count = ActiveWorkbook.Worksheets.Count
  
       ' Begin the loop.
       For I = 2 To WS_Count
  
          ' Insert your code here.
          'strTabs = strTabs &amp; " " &amp; ActiveWorkbook.Worksheets(I).Name
          strTabs = strTabs &amp; "(SUMIF('" &amp; ActiveWorkbook.Worksheets(I).Name &amp; "'!L3:L4,c10,'" &amp; ActiveWorkbook.Worksheets(I).Name &amp; "'!M3:M4)+"
  
          ' The following line shows how to reference a sheet within
          ' the loop by displaying the worksheet name in a dialog box.
          'MsgBox ActiveWorkbook.Worksheets(I).Name
          'MsgBox strTabs
  
       Next I
  
          strTabs = "=" &amp; (Left(strTabs, Len(strTabs) - 1)) &amp; "))"
          'Range("J19").Formula = MsgBox(strTabs)  '&lt;-- this is working (it doesn't throw an error but it doesn't return the correct results)
          'Range("J19").Value = strTabs
          'Range("J20").Formula = strTabs 'this line throws object-defined error
          HoursFlown = strTabs
    End Function
  </code></pre>