SUMIF cells with a specific column header from multiple worksheets - Using VBA

[isanka88]

Hi All Good people

I have a workbook and macro to calculate sum from multiple worksheets and put it to master sheet. Below is my current macro which works fine. But I need to add one more additional condition to SUM. I think this can be done by using sumif. I have attached my workbook below explaining about my current outcome and expected outcome. Kindly have a look

Msaster Workbook.xlsx

Master Sheet Name,051120,061120 Now Result,85827,73143,This is the Result what I get from my exiting macro Expected Results,68408,41656,This is the Result what I am expecting to get

docs.google.com

Sub GetSums()
Dim lngCol As Long, wsName As Range
With Sheets("Master")
If .[C3] <> "" Then .Range("C3", .[B3].End(2)) = ""
For Each wsName In .Range("C2", .[B2].End(2))
lngCol = Sheets(wsName.Value).Rows(1).Find("My Text").Column
wsName.Offset(1).Value = _
Evaluate("Sum('" & wsName.Value & "'!" & Cells(3, lngCol + 1).Address & ":" & _
Sheets(wsName.Value).Cells(Rows.Count, lngCol + 1).End(3).Address & ")")
Next wsName
End With
End Sub

 

[Alex Blakenburg]

See if this works for you.

Sub GetSumIfs()
    Dim lngCol As Long, wsName As Range
    
    Dim rngSum As Range
    Dim rngDept As Range
    Dim lastRow As Long
    
    With Sheets("Master")
        If .[C3] <> "" Then .Range("C3", .[B3].End(2)) = ""
            For Each wsName In .Range("C2", .[B2].End(2))
                lngCol = Sheets(wsName.Value).Rows(1).Find("My Text").Column
                With Worksheets(wsName.Value)
                    lngCol = .Rows(1).Find("My Text").Column
                    lastRow = .Cells(Rows.Count, lngCol + 1).End(xlUp).Row
                    
                    Set rngSum = .Range(.Cells(3, lngCol + 1).Address & ":" & .Cells(lastRow, lngCol + 1).Address)
                    Set rngDept = .Range(.Cells(3, 2).Address & ":" & .Cells(lastRow, 2).Address)
                    
                    wsName.Offset(1).Value = Application.SumIfs(rngSum, rngDept, "ABC")
                                      
                End With
                
            Next wsName
    End With
End Sub

 

[isanka88]

Hi Alex
Yes it did work. Awesome!!!!!!!!! I am out of words to say thank you. You gave me an amazing answer which I have been struggling to get for past 3 months. I really appreciate your time for this. Thank you once again. ????????

 

[Alex Blakenburg]

LOL. Loved your feedback. Happy to help.