Excel formula: To sum the same cell values from multiple sheets with names as dates

Question

Is there a way to sum all the values of the same cell of multiple sheets (tab) which the name of each tab is date, and show the summed value in the MasterSheet?

For example:

Currently, I use this formula =SUM(INDIRECT("'*-"&MONTH(C2)&"-2020'!$E$3")) in the cell under the "Oct-2020" cell, as shown in the figure below, and I will do the same for Nov-20 and Dec-20.

As you can see, I am getting this "#REF!" error. Currently, I have two tabs that I am trying to get the "$E$3" cell values from, "13-10-2020" and "14-10-2020". However, if I only have one sheet (let's say "14-10-2020" only), I am able to get the value.

Does anyone know what is going on with my formula? Why it works when there is only one sheet (tab) to read from, but does not work when there are multiple sheets (tabs), even I used "*" to include all dates.

Please advice. Thank you in advanced

Answer 1

I think this should solve your problem:

Option Explicit

Sub sub_sum_up_months()
    Dim wks As Worksheet
    Dim nr_month As Integer, nr_year As Integer
    Dim str_month As String, str_year As String
    Dim c As Integer
    Dim my_Sum As Double

    For c = 3 To 5 ' Iterating over the columns "C" to "E"
        my_Sum = 0
    
        nr_month = month(Worksheets("MasterSheet").Cells(2, c).Value)
        If nr_month < 10 Then
            str_month = "0" & nr_month
        Else
            str_month = CStr(nr_month)
        End If
        
        nr_year = Year(Worksheets("MasterSheet").Cells(2, c).Value)
        str_year = CStr(nr_year)
        
        For Each wks In ThisWorkbook.Worksheets
            If Right(wks.Name, 4) = str_year And Left(Right(wks.Name, 7), 2) = str_month Then
                my_Sum = my_Sum + wks.Cells(3, 5) 'cells(3,5) is "E3"
            End If
        Next wks
        Worksheets("MasterSheet").Cells(3, c).Value = my_Sum
    Next c
End Sub