EXCEL VBA: Condensing multiple ranges with a SUMIF formula to one line

I have the following lines of code to fill specific ranges with SUMIF formulas:

Range("B7").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B8").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B9").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B12").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B13").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B17").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B21").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B22").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B23").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B24").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B25").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B29").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B30").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B31").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B32").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B33").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"
Range("B37").FormulaR1C1 = "=SUMIF(R2C13:R500C13,RC[-1],R2C12:R500C12)"

I'm having a hard time turning the above lines of code into something shorter like the code below. I receive the same answer but I am unsure how to code it with multiple ranges.

Dim Sumact As Range

Set Sumact = Sheets("IS Branch 12").Range("B7")

Sumact = Application.sumif(Range("M2:M500"),Range("A7"),Range("L2:L500"))

3 answers

  • answered 2017-11-14 23:53 sktneer

    Why not try it this way?

    Dim Rng As Range
    Set Rng = Range("B7:B9,B12:B13,B17,B21:B25,B29:B33,B37")
    Rng.Formula = "=SUMIF($M$2:$M$500,A7,$L$2:$L$500)"
    Rng.Value = Rng.Value
    

  • answered 2017-11-15 00:00 R. Roe

    To make it even shorter you could set the range values like so;

    [B7:B9,B12:B13,B17,B21:B25,B29:B33,B37].Formula = "=SUMIF($M$2:$M$500,A7,$L$2:$L$500)"
    

    The [] is a shortcut for the range object

  • answered 2017-11-15 00:39 Jeeped

    I was under the impression that you wanted to reduce the calculation to a single code line. You can with the SUM(SUMIF(..., ..., ...)) formula but the series of criteria values needs to be an array, not cells and it best to remove duplicates.

    =sum(sumifs(L2:L500, M2:M500, {1, 2, 3, 4, 5}))
    

    In code to collect the values from the discontiguous range into an array while removing duplicate values,

    Dim a As Long, arr As Variant, rng As Range
    ReDim arr(30)  'total 31 possible values before duplicate removal
    
    For Each rng In Range("a7:a9,a12:a13,a17,a21:a25,a29:a33,a37")
        Debug.Print rng.Address
        If IsError(Application.Match(rng.Value2, arr, 0)) Then
            arr(a) = rng.Value2
            a = a + 1
        End If
    Next rng
    ReDim Preserve arr(a - 1)
    Debug.Print Application.Sum(Application.SumIfs(Range("L2:L500"), Range("M2:M500"), arr))
    
    Dim Sumact As Range
    Set Sumact = Sheets("IS Branch 12").Range("B7")
    Sumact = Application.Sum(Application.SumIfs(Range("L2:L500"), Range("M2:M500"), arr))