Sumifs in VBA where all conditions met

Question

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 there so 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 VBA (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)

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

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.

Just checking: Have you tried using the formula without the VBA to check that the formula itself is working as expected? — Mistella
Hi Mistella - Thanks for replying. Yes I have, it works fine in the spreadsheet... — Jerbs
Where are you setting i? Is it a single number, or part of a range (e.g. "3:H10")? — Chronocidal
So what I've posted is nested within a for loop, for i = 2 to LastRow — Jerbs
Also - your Excel and VBA formula are pointing to completely different columns? Is this deliberate, or have you mis-posted one of them? — Chronocidal

Chronocidal Chronocidal · Accepted Answer · 2018-11-19T13:23:45

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.)

Sumifs in VBA where all conditions met

1 Answers