Sum from the last Monday of the month previous

Suggest this user defined function

Function CountSpecial(AnyCell As Range) As Long
Dim MySheet As Worksheet
Dim MySheetIndex As Long
Dim LastMondayRow As Long
Dim LastSundayRow As Long


Set MySheet = AnyCell.Parent
MySheetIndex = MySheet.Index
For N = MySheet.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.Weekday(MySheet.Cells(N, 1)) = 1 Then
            LastSundayRow = N
            Exit For
        End If
    Next N
If MySheetIndex > 1 Then
    
    For N = MySheet.Parent.Sheets(MySheetIndex - 1).Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.Weekday(MySheet.Parent.Sheets(MySheetIndex - 1).Cells(N, 1)) = 2 Then
            LastMondayRow = N
            Exit For
        End If
    Next N
    CountSpecial = MySheet.Parent.Sheets(MySheetIndex - 1).Cells(Rows.Count, 1).End(xlUp).Row - LastMondayRow + LastSundayRow - 1

Else
    LastMondayRow = 3
End If
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter =CountSpecial(F20) or any other cell on the sheet - doesn't matter which

Remember to save the workbook as a macro enabled workbook .xlsm

OK

New name for the function as it is summing

Function SumSpecial(AnyCell As Range) As Long
Dim MySheet As Worksheet
Dim MySheetIndex As Long
Dim LastMondayRow As Long
Dim LastSundayRow As Long


Set MySheet = AnyCell.Parent
MySheetIndex = MySheet.Index
For N = MySheet.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.Weekday(MySheet.Cells(N, 1)) = 1 Then
            LastSundayRow = N
            Exit For
        End If
    Next N
If MySheetIndex > 1 Then
    
    For N = MySheet.Parent.Sheets(MySheetIndex - 1).Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.Weekday(MySheet.Parent.Sheets(MySheetIndex - 1).Cells(N, 1)) = 2 Then
            LastMondayRow = N
            Exit For
        End If
    Next N
    SumSpecial = WorksheetFunction.Sum(MySheet.Parent.Sheets(MySheetIndex - 1).Range(MySheet.Parent.Sheets(MySheetIndex - 1).Cells(Rows.Count, 3).End(xlUp), MySheet.Parent.Sheets(MySheetIndex - 1).Cells(LastMondayRow, 3))) + WorksheetFunction.Sum(MySheet.Range(MySheet.Cells(3, 3), MySheet.Cells(LastSundayRow, 3)))

Else
    LastMondayRow = 3
End If
SumSpecial = WorksheetFunction.Sum(MySheet.Range(MySheet.Cells(3, 3), MySheet.Cells(LastSundayRow, 3)))
End Function

I note that cell A33 on the sheet1 tab contains a value for a Monday even though it is not visible. You need to delete this to avoid confusing the formula.even

Please see the attached. I did find a bug where I had two lines in the wrong order.

Try

Application.Volatile

immediately after the first line