Ytukyg

I'm trying to code the following in VBA:

=SUMIFS(N2:N29,D2:D29,IF(COUNTIF(D2:D29,A36)=0,"*","="&A36),C2:C29,IF(COUNTIF(C2:C29,B36)=0,"*","="&B36),E2:E29,IF(COUNTIF(E2:E29,C36)=0,"*","="&C36),F2:F29,IF(COUNTIF(F2:F29,D36)=0,"*","="&D36))

The nested If and Countif are in there so that the Sumif ignores any condition that isn't required by the user (the user inputs A:B36), but still sums cells that meet all the remaining conditions.

I've written the following in vb (with criterias 1-3 determined in the same way):

If Application.WorksheetFunction.CountIf(Worksheets("Benchmark").Range("V" & i), Box4.Value) = 0 Then

    criteria4 = "*"

Else

    criteria4 = "=" & Box4.Value

End If



If criteria1 = "*" And criteria2 = "*" And criteria3 = "*" And criteria4 = "*" Then

    dummy = 0

Else

    dummy = Application.WorksheetFunction.SumIfs(Worksheets("Benchmark").Range("H" & i), Worksheets("Benchmark").Range("S" & i), criteria1, Worksheets("Benchmark") _

    .Range("R" & i), criteria2, Worksheets("Benchmark").Range("C" & i), criteria3, Worksheets("Benchmark").Range("V" & i), criteria4)

but what is happening is that the sumifs is summing all cells that meet at least one of the conditions supplied, rather than all of them...

Does anyone know where I'm going wrong? Appreciate that this is a bit of a convoluted question, hope it makes sense...

Edit: To add context, the purpose of this code is to loop through a range, and find cells that meet criteria supplied by the user. However, if the user opts to supply certain criteria but not others, then those criteria not supplied are ignored. I've used a sumifs because my understanding is that the function will sum all cells that meet the set of criteria supplied. I can then say, if sumifs>0 then cell meets criteria supplied by the user

Here is a version bodged together based on additional information garnered through the comments, that dispenses with the SumIfs entirely:

Dim dummy AS Long, IsValid AS Boolean



For i = 2 to LastRow

    If Box1.Value & Box2.Value & Box2.Value & Box4.Value = "****" Then

        dummy = 0

    Else

        IsValid = (Worksheets("Benchmark").Cells(i, "S").Value LIKE Box1.Value) 

        IsValid = IsValid And (Worksheets("Benchmark").Cells(i, "R").Value LIKE Box2.Value) 

        IsValid = IsValid And (Worksheets("Benchmark").Cells(i, "C").Value LIKE Box3.Value) 

        IsValid = IsValid And (Worksheets("Benchmark").Cells(i, "V").Value LIKE Box4.Value) 



        If IsValid Then

            dummy = Worksheets("Benchmark").Cells(i, "H").Value

        Else

            dummy = 0

        End If

    End If



    'Code to do stuff with dummy goes here



Next i

For each iteration of the For loop, we are using a Boolean variable to track if the row is valid, and LIKE statements to check each criteria. The 2nd comparison onwards use AND to make sure that you remember if the criteria has already failed.

A LIKE statement is similar to an = statement, but allows Wildcards - so, for example "SomeText" = "SomeOtherText" and "SomeText" LIKE "SomeOtherText" will both be False, whereas "Some*Text" = "SomeOtherText" will be False while "Some*Text" LIKE "SomeOtherText" will be True - since the "*" means "any text can go here".

(SumIfs uses a LIKE comparison instead of an = comparison, unless you specifically specify = at the start of the Condition.)