Search code examples
excelformulassumifsvba

SUMIFS in VBA formula using criteria

I am relatively new to Excel VBA script and am having trouble with the syntax quotes here on this line

ActiveSheet.Range("H" & EndRowH + 2).Formula = "=SUMIFS(H2:H &EndRowH & ,C1:C1000,&"">=""&lngStart&)"

Let me know if you need any other details.

Solution
    1. You had some basic string concatenation syntax errors.

    2. Both the criteria range and the sum range need to be the same number of rows.

      ActiveSheet.Range("H" & EndRowH + 2).Formula = "=SUMIFS(H2:H" & EndRowH & ", C2:C" & EndRowH & ", "">=" & lngStart &""")"

    3. Your original formula had the sum range start at H2 while the criteria range started at C1. While you can offset the sum range and the criteria range if necessary, they still have to be the same number of rows.

      ActiveSheet.Range("H" & EndRowH + 2).Formula = "=SUMIFS(H2:H" & EndRowH & ", C1:C" & EndRowH - 1 & ", "">=" & lngStart &""")"