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

isanka88

New Member
Joined
Dec 12, 2020
Messages
13
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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


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

1628392317636.png

1628392340963.png

1628392366744.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
See if this works for you.

VBA Code:
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
 
Upvote 0
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. ????????
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top