T4209_Using SUMIFS In VBA From Multiple Sheets

https://excel-egy.com/forum/t4209
---------------------------------

Sub Using_SUMIFS_In_VBA_From_Multiple_Sheets()
    Dim e, ws As Worksheet, wsSource As Worksheet, wf As Object, iCol As Integer, iStart As Integer, iEnd As Integer, i As Integer, rngB As Range, rngC As Range, rngX As Range

    Application.ScreenUpdating = False
        Set ws = ThisWorkbook.Worksheets("اجمالى المبيعات")
        Set wf = Application.WorksheetFunction
        ws.Range("B5:F" & Rows.Count).ClearContents

        For Each e In Array("XLSB", "XLSM", "XLS")
            If e = "XLSB" Then
                Set wsSource = ThisWorkbook.Worksheets("XLSB_SEARCH"): iCol = 16: iStart = 5: iEnd = 10
            ElseIf e = "XLSM" Then
                Set wsSource = ThisWorkbook.Worksheets("XLSM_SEARCH"): iCol = 17: iStart = 11: iEnd = 12
            ElseIf e = "XLS" Then
                Set wsSource = ThisWorkbook.Worksheets("XLS_SEARCH"): iCol = 19: iStart = 13: iEnd = 15
            End If

            Set rngB = wsSource.Range("B:B")
            Set rngC = wsSource.Range("C:C")
            Set rngX = wsSource.Columns(iCol)

            With ws
                For i = iStart To iEnd
                    ws.Cells(i, 2) = wf.SumIfs(rngB, rngX, ws.Cells(i, 1))
                    ws.Cells(i, 3) = wf.SumIfs(rngC, rngX, ws.Cells(i, 1))
                Next i
            End With
        Next e

        ws.Cells(16, 2) = wf.Sum(ws.Range("B5", "B15"))
        ws.Cells(16, 3) = wf.Sum(ws.Range("C5", "C15"))
        For i = 5 To 16
            ws.Cells(i, 6) = wf.Sum((ws.Cells(i, 3)) - (ws.Cells(i, 4)))
        Next i
    Application.ScreenUpdating = True
End Sub