Search code examples
excelformula

Using SUMIFS formula with dynamic range

I wonder if there is a way I can use the below SUMIFS formula on a dynamic range without having to click on the formula and drag down to capture the whole range manually every time

=SUMIFS($I$58:$I$573,$J$58:$J$573,"OK",$F$58:$F$573,C3,$C$58:$C$573,B3)
Solution

  • If the range size is fixed, I suggest that you can use the SUMIFS function and wrapped OFFSET function inside it.

    OFFSET(reference, rows, cols, [height], [width])

    Use the $I$58 as the reference in offset function, and replace the sum_range, criteria_range1, etc. in SUMIFS function.

    Next time, just change some numbers in the formula, but you need to do some calculators.

    Hope it can be helpful.

    Note:

    =SUMIF(A2:A10,A2,B2:B10)
=SUMIF(OFFSET(A2,0,0,9,1),A2,OFFSET(B2,0,0,9,1))

=SUMIFS(I58:I573,J58:J573,"OK",F58:F573,C3,C58:C573,B3)
=SUMIFS(OFFSET(I58,0,0,516,1),OFFSET(J58,0,0,516,1),"OK",OFFSET(F58,0,0,516,1),C3,OFFSET(C58,0,0,516,1),B3)