CODEWITHSUNDEEP
excelvba
Przemek Dabek
Przemek Dabek

Reputation: 533

VBA Sumifs under two conditions

I want to create a function that will sum up for me

CRD_EKSP_PIER_DC_FIN = "CRD_EKSP_PIER_DC_FIN"
CRD_KOR_DC_FIN = "CRD_KOR_DC_FIN"

When two conditions are met, where DIFFRENT> 365 and CRD_RWG <1.5 However, my macro shows me a result of 0. Where do I go wrong?

Set wbMe = ActiveWorkbook
CRD_EKSP_PIER_DC_FIN = "CRD_EKSP_PIER_DC_FIN"
CRD_KOR_DC_FIN = "CRD_KOR_DC_FIN"
DIFFRENT = "DIFFRENT"
CRD_RWG = "CRD_RWG"
With wbMe.Sheets("NPE")
If .AutoFilterMode Then .AutoFilterMode = False
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    Set rTable = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
    Set rCol = rTable.Rows(1).Find(What:=DIFFRENT, LookIn:=xlValues, LookAt:=xlWhole)
    Set rCol1 = rTable.Rows(1).Find(What:=CRD_EKSP_PIER_DC_FIN, LookIn:=xlValues, LookAt:=xlWhole)
    Set rCol2 = rTable.Rows(1).Find(What:=CRD_KOR_DC_FIN, LookIn:=xlValues, LookAt:=xlWhole)
    Set rCol3 = rTable.Rows(1).Find(What:=CRD_RWG, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rCol Is Nothing Then
        .Range("T39").Value = Application.WorksheetFunction.SumIfs(rTable.Columns(rCol.Column), rTable.Columns(rCol.Column), ">365", rTable.Columns(rCol3.Column), "<1.5", rTable.Columns(rCol1.Column), "CRD_EKSP_PIER_DC_FIN", rTable.Columns(rCol2.Column), "CRD_KOR_DC_FIN")
    End If
End With

My date :

enter image description here

Upvotes: 0

Views: 55

Answers (1)

Cyril
Cyril

Reputation: 6829

Trying to set-up your data:

enter image description here

Making a change to your code, since:

  • rTable does not seem necessary at all,
  • the if statement wrapping your sumifs() keeps triggering the false state,
  • going to put in checks for each column being found (removes ranges, uses column values),
  • going to simplify some variables (rCol & rColString to ensure things match up) and dimensions, and
  • i don't want to scroll for numbers, so outputting to F1:
Sub test()
    Dim rColString As String
    rColString = "DIFFRENT"
    Dim rCol1String As String
    rCol1String = "CRD_EKSP_PIER_DC_FIN"
    Dim rCol2String As String
    rCol2String = "CRD_KOR_DC_FIN"
    Dim rCol3String As String
    rCol3String = "CRD_RWG"
    With ActiveWorkbook.Sheets("NPE")
    If .AutoFilterMode Then .AutoFilterMode = False
        Dim rCol As Long
        rCol = .Rows(1).Find(What:=rColString, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol1 As Long
        rCol1 = .Rows(1).Find(What:=rCol1String, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol2 As Long
        rCol2 = .Rows(1).Find(What:=rCol2String, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol3 As Long
        rCol3 = .Rows(1).Find(What:=rCol3String, LookIn:=xlValues, LookAt:=xlWhole).Column
        .Range("F1").Value = WorksheetFunction.SumIfs(.Columns(rCol), .Columns(rCol), ">365", .Columns(rCol3), "<1.5", .Columns(rCol1), "CRD_EKSP_PIER_DC_FIN", .Columns(rCol2), "CRD_KOR_DC_FIN")
    End With
End Sub

After running the modified macro:

enter image description here

One thing that strikes me as odd is that you're searching for the LABEL in each row, for your sumifs(), i.e., "CRD_EKSP_PIER_DC_FIN" and "CRD_KOR_DC_FIN", so if you have actual values in the columns of the same header, then that would be a place to check.

Edit1:

Your data suggested that this is incorrect:

.Range("F1").Value = WorksheetFunction.SumIfs(.Columns(rCol), .Columns(rCol), ">365", .Columns(rCol3), "<1.5", .Columns(rCol1), "CRD_EKSP_PIER_DC_FIN", .Columns(rCol2), "CRD_KOR_DC_FIN")

You are checking that the value for each row in .Columns(rCol1) is "CRD_EKSP_PIER_DC_FIN", and similarly .Columns(rCol2) is "CRD_KOR_DC_FIN", which neither is true... hence always getting a zero result. This should work:

Sub test()
    Dim rColString As String
    rColString = "DIFFRENT"
    Dim rCol1String As String
    rCol1String = "CRD_EKSP_PIER_DC_FIN"
    Dim rCol2String As String
    rCol2String = "CRD_KOR_DC_FIN"
    Dim rCol3String As String
    rCol3String = "CRD_RWG"
    With ActiveWorkbook.Sheets("NPE")
    If .AutoFilterMode Then .AutoFilterMode = False
        Dim rCol As Long
        rCol = .Rows(1).Find(What:=rColString, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol1 As Long
        rCol1 = .Rows(1).Find(What:=rCol1String, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol2 As Long
        rCol2 = .Rows(1).Find(What:=rCol2String, LookIn:=xlValues, LookAt:=xlWhole).Column
        Dim rCol3 As Long
        rCol3 = .Rows(1).Find(What:=rCol3String, LookIn:=xlValues, LookAt:=xlWhole).Column
        .Range("F1").Value = WorksheetFunction.SumIfs(.Columns(rCol), .Columns(rCol), ">365", .Columns(rCol3), "<1.5")
    End With
End Sub

Upvotes: 1

Related Questions