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

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

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