VBA : Sumifs returning zero

debugcn Published at Dev

Ihab Benamer

I have a big problem with my script VBA, I want to create a script VBA which will enable to make the sum of some cells on a sheet and to display them on another sheet according to some criteria.
But the function SumIfs is returning zero value.

This is my script:

If Worksheets("Test").Range("B2").Text = Worksheets("Nomen").Range("K3").Text Then
    Worksheets("Test").Range("C23").Value = Application.WorksheetFunction.SumIfs(Worksheets("DETAILS").Range("H2:H174"), Worksheets("DETAILS").Range("B2:B174"), Worksheets("Nomen").Range("K3"), Worksheets("DETAILS").Range("J2:J174"), Worksheets("Test").Range("C2")
End If

Picture1

Picture2

QHarr

You are trying to sum text hence zero.

I put into variables to make easier to debug.

Sub test()
Dim wb As Workbook
Set wb = ThisWorkbook

Dim sumRange As Range
Set sumRange = wb.Worksheets("DETAILS").Range("H2:H174")

Dim critRange1 As Range
Set critRange1 = Worksheets("DETAILS").Range("B2:B174")

Dim crit1 As Variant
crit1 = Worksheets("Nomen").Range("K3")

Dim critRange2 As Range
Set critRange2 = Worksheets("DETAILS").Range("J2:J174")

Dim crit2 As Variant
crit2 = Worksheets("Test").Range("C2")

 MsgBox Application.WorksheetFunction.SumIfs(sumRange, critRange1, crit1, critRange2, crit2)


End Sub

If I fill each range with 1 and also try to match on one I get 174 back. If I populate sumRange with "A", in the same way you have used text, I get 0. i.e. if I replace all the 1's with A.

Collected from the Internet

Please contact [email protected] to delete if infringement.